using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.ComponentModel;

[DataObject(true)]
public class Common_FilesStorageData : SqlData
{
    public Common_FilesStorageData()
    {
        DataConnection = ConfigurationManager.ConnectionStrings["ConnString_SEST"].ConnectionString;
        TableName = "Common_FilesStorage";
        PrimaryKeyColumnNames = new string[] { "FilesStorageID" };
    }


    public DataTable Select(string PageName, string PageKeys, string FileName) {
        DataTable dt = new DataTable(TableName);

        using (SqlConnection cn = new SqlConnection(DataConnection))
        using (SqlDataAdapter da = new SqlDataAdapter(
            "SELECT [FilesStorageID]" + 
            ",PageName" + 
            ", PageKeys" +
            ",CAST(YEAR([CreateDate]) AS varchar(4)) + '/' + CAST(MONTH([CreateDate]) AS varchar(2)) + '/' + CAST(DAY([CreateDate]) AS varchar(2)) AS CreateDate " +
            ",PhysicalPath" +
            ",URLPath" + 
            ",FileName" +
            ",ExpireDate" +
             " FROM " + TableName +
             " Where PageName=@PageName and PageKeys=@PageKeys and FileName=@FileName", cn)) {
            da.SelectCommand.CommandType = CommandType.Text;
            da.SelectCommand.Parameters.AddWithValue("@PageName", PageName);
            da.SelectCommand.Parameters.AddWithValue("@PageKeys", PageKeys);
            da.SelectCommand.Parameters.AddWithValue("@FileName", FileName);
            
            da.Fill(dt);

            return dt;
        }
    }
    public DataTable Select(string PageName, string PageKeys) {
        DataTable dt = new DataTable(TableName);

        using (SqlConnection cn = new SqlConnection(DataConnection))
        using (SqlDataAdapter da = new SqlDataAdapter(
            "SELECT [FilesStorageID]" +
            ",PageName" +
            ", PageKeys" +
            ",CAST(YEAR([CreateDate]) AS varchar(4)) + '/' + CAST(MONTH([CreateDate]) AS varchar(2)) + '/' + CAST(DAY([CreateDate]) AS varchar(2)) AS CreateDate " +
            ",PhysicalPath" +
            ",URLPath" +
            ",FileName" +
            ",ExpireDate" +
             " FROM " + TableName +
             " Where PageName=@PageName and PageKeys=@PageKeys Order by CreateDate ", cn)) {
            da.SelectCommand.CommandType = CommandType.Text;
            da.SelectCommand.Parameters.AddWithValue("@PageName", PageName ?? "NoPageName");
            da.SelectCommand.Parameters.AddWithValue("@PageKeys", PageKeys ?? "NoKeys");

            da.Fill(dt);

            return dt;
        }
    }
    public DataTable SelectMultiKeys(string PageName, string PageKeys) {
        DataTable dt = new DataTable(TableName);

        using (SqlConnection cn = new SqlConnection(DataConnection))
        using (SqlDataAdapter da = new SqlDataAdapter(
            "SELECT [FilesStorageID]" +
            ",PageName" +
            ", PageKeys" +
            ",CAST(YEAR([CreateDate]) AS varchar(4)) + '/' + CAST(MONTH([CreateDate]) AS varchar(2)) + '/' + CAST(DAY([CreateDate]) AS varchar(2)) AS CreateDate " +
            ",PhysicalPath" +
            ",URLPath" +
            ",FileName" +
            ",ExpireDate" +
             " FROM " + TableName +
             " Where PageName=@PageName and PageKeys in (" + PageKeys + ") Order by CreateDate ", cn)) {
            da.SelectCommand.CommandType = CommandType.Text;
            da.SelectCommand.Parameters.AddWithValue("@PageName", PageName ?? "NoPageName");

            da.Fill(dt);

            return dt;
        }
    }


    [DataObjectMethod(DataObjectMethodType.Insert, true)]
    public void Insert(string PageName, string PageKeys, string FileName, string PhysicalPath, string URLPath, string ExpireDate) {
        using (SqlConnection cn = new SqlConnection(DataConnection))
        using (SqlCommand cm = new SqlCommand(
            "INSERT INTO " + TableName +
            " ([PageName],[PageKeys],[FileName],[PhysicalPath],[URLPath],[ExpireDate]) " +
            " VALUES " +
            " (@PageName, @PageKeys, @FileName, @PhysicalPath, @URLPath, @ExpireDate)", cn)) {
            cn.Open();

            if (ExpireDate == "")
                ExpireDate = "9999/12/31";

            cm.CommandType = CommandType.Text;
            cm.Parameters.AddWithValue("@PageName", PageName.Trim());
            cm.Parameters.AddWithValue("@PageKeys", PageKeys.Trim());
            cm.Parameters.AddWithValue("@FileName", FileName.Trim());
            cm.Parameters.AddWithValue("@PhysicalPath", PhysicalPath == null ? Convert.DBNull : PhysicalPath.Trim());
            cm.Parameters.AddWithValue("@URLPath", URLPath.Trim());
            cm.Parameters.AddWithValue("@ExpireDate", ExpireDate == null ? Convert.DBNull : ExpireDate.Trim());

            cm.ExecuteNonQuery();

            cn.Close();
        }
    }

    [DataObjectMethod(DataObjectMethodType.Delete, true)]
    public void Delete(string Original_FilesStorageID) {
        using (SqlConnection cn = new SqlConnection(DataConnection))
        using (SqlCommand cm = new SqlCommand("DELETE " + TableName + " WHERE FilesStorageID=@Original_FilesStorageID", cn)) {
            cn.Open();

            cm.CommandType = CommandType.Text;
            SqlParameter parameter = cm.Parameters.AddWithValue("@Original_FilesStorageID", Original_FilesStorageID);
            parameter.SourceVersion = DataRowVersion.Original;

            cm.ExecuteNonQuery();

            cn.Close();
        }
    }
    [DataObjectMethod(DataObjectMethodType.Delete, true)]
    public void Delete(string PageName, string PageKeys) {
        using (SqlConnection cn = new SqlConnection(DataConnection))
        using (SqlCommand cm = new SqlCommand("DELETE " + TableName +
            " WHERE PageName=@PageName and PageKeys=@PageKeys", cn)) {
            cn.Open();

            cm.CommandType = CommandType.Text;
            cm.Parameters.AddWithValue("@PageName", PageName.Trim());
            cm.Parameters.AddWithValue("@PageKeys", PageKeys.Trim());
            cm.ExecuteNonQuery();

            cn.Close();
        }
    }
    [DataObjectMethod(DataObjectMethodType.Delete, true)]
    public void Delete(string PageName, string PageKeys, string FileName) {
        using (SqlConnection cn = new SqlConnection(DataConnection))
        using (SqlCommand cm = new SqlCommand("DELETE " + TableName +
            " WHERE PageName=@PageName and PageKeys=@PageKeys and FileName=@FileName", cn)) {
            cn.Open();

            cm.CommandType = CommandType.Text;
            cm.Parameters.AddWithValue("@PageName", PageName.Trim());
            cm.Parameters.AddWithValue("@PageKeys", PageKeys.Trim());
            cm.Parameters.AddWithValue("@FileName", FileName.Trim());
            cm.ExecuteNonQuery();

            cn.Close();
        }
    }

}
