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

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


    [DataObjectMethod(DataObjectMethodType.Select, true)]
    public DataTable Select(string Condition)
    {
        DataTable dt = new DataTable(TableName);
        using (SqlConnection cn = new SqlConnection(DataConnection))
        using (SqlDataAdapter da = new SqlDataAdapter(
            "SELECT [PettyCashID]" + 
            " ,[UserName]" +
            " ,[Item]" +
            //" ,[ReceiptDate]" +
            ",CAST(YEAR([ReceiptDate]) AS varchar(4)) + '/' + CAST(MONTH([ReceiptDate]) AS varchar(2)) + '/' + CAST(DAY([ReceiptDate]) AS varchar(2)) AS ReceiptDate " +
            //" ,[PayDate]" +
            ",CAST(YEAR([PayDate]) AS varchar(4)) + '/' + CAST(MONTH([PayDate]) AS varchar(2)) + '/' + CAST(DAY([PayDate]) AS varchar(2)) AS PayDate " +
            " ,[Amount]" +
            " FROM " + TableName + 
            //" WHERE " + Condition.Replace("--","").Replace("''","") + " and (DATEDIFF(mm, ReceiptDate, GETDATE()) < 12) " +
            " WHERE " + Condition.Replace("--","") +
            " Order by ReceiptDate DESC ", cn))
        {
            da.Fill(dt);
            return dt;
        }
    }
    //[DataObjectMethod(DataObjectMethodType.Select, true)]
    //public DataTable SelectByAnnouncementID(String AnnouncementID) {
    //    DataTable dt = new DataTable(TableName);
    //    using (SqlConnection cn = new SqlConnection(DataConnection))
    //    using (SqlDataAdapter da = new SqlDataAdapter(
    //        //"SELECT * FROM " + TableName + " ORDER BY " + PrimaryKeyColumnNames[0], cn))
    //        "SELECT [AnnouncementID]" +
    //        " ,[Creator]" +
    //        " ,[PostFrom]" +
    //        " ,[CreateDate]" +
    //        " ,[StartDate]" +
    //        " ,[EndDate]" +
    //        " ,[Importance]" +
    //        " ,[Subject]" +
    //        " ,[Contents]" +
    //        " ,[AttachedFile]" +
    //        " FROM " + TableName + " Where AnnouncementID='" + AnnouncementID.Replace("--","").Replace("''","").Trim() + "'", cn)) {
    //        da.Fill(dt);
    //        return dt;
    //    }
    //}


    [DataObjectMethod(DataObjectMethodType.Insert, true)]
    public void Insert(string UserName, string Item, string ReceiptDate, string PayDate, string Amount) {
        
        using (SqlConnection cn = new SqlConnection(DataConnection))
        using (SqlCommand cm = new SqlCommand(
            "INSERT INTO " + TableName +
            " ([UserName],[Item],[ReceiptDate],[PayDate],[Amount]) " +
            " VALUES " +
            " (@UserName, @Item, @ReceiptDate, @PayDate, @Amount)", cn)) {
            cn.Open();

            cm.CommandType = CommandType.Text;
            cm.Parameters.AddWithValue("@UserName", UserName.Trim());
            cm.Parameters.AddWithValue("@Item", Item.Trim());
            cm.Parameters.AddWithValue("@ReceiptDate", ReceiptDate.Trim());
            cm.Parameters.AddWithValue("@PayDate", PayDate.Trim());
            cm.Parameters.AddWithValue("@Amount", Amount.Trim());

            cm.ExecuteNonQuery();

            cn.Close();
        }
    }


    [DataObjectMethod(DataObjectMethodType.Update, true)]
    public void Update(string UserName, string Item, string ReceiptDate, string PayDate, string Amount, string original_PettyCashID) {
        using (SqlConnection cn = new SqlConnection(DataConnection))
        using (SqlCommand cm = new SqlCommand(
            "UPDATE " + TableName +
            " SET [UserName]=@UserName, " +
            " [Item]=@Item, " +
            " [ReceiptDate]=@ReceiptDate, " +
            " [PayDate]=@PayDate, " +
            " [Amount]=@Amount " +
            " Where PettyCashID=@original_PettyCashID", cn)) {
            cn.Open();
            ReceiptDate = ReceiptDate.Trim();
            PayDate = PayDate.Trim();
            if (ReceiptDate.Length <=0)
                ReceiptDate = null;
            if (PayDate.Length <=0)
                PayDate = null;

            cm.CommandType = CommandType.Text;
            cm.Parameters.AddWithValue("@UserName", UserName.Trim());
            cm.Parameters.AddWithValue("@Item", Item.Trim());
            cm.Parameters.AddWithValue("@ReceiptDate", ReceiptDate ?? Convert.DBNull);
            cm.Parameters.AddWithValue("@PayDate", PayDate ?? Convert.DBNull);
            cm.Parameters.AddWithValue("@Amount", Amount.Trim());
            SqlParameter parameter = cm.Parameters.AddWithValue("@original_PettyCashID", original_PettyCashID);
            parameter.SourceVersion = DataRowVersion.Original;

            cm.ExecuteNonQuery();

            cn.Close();
        }
    }

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

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

            cm.ExecuteNonQuery();

            cn.Close();
        }
    }

}
