using framework_business;
using framework_library;
using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Reflection;
using System.Web;
using System.Windows.Forms;

/// <summary>
/// Summary description for Loyalty
/// </summary>
public class Loyalty
{
    string _dbName = "";

    public Loyalty(string dbName)
    {
        _dbName = dbName;
    }

    /// <summary>
    /// Send today's loyalty members
    /// </summary>
    public string SendLoyaltyMembers(string recipients)
    {
        string returnString = "";
        try
        {
            DataTable dtLoyaltyDetail = new DataTable();
            SqlCommand command = new SqlCommand();

            //string query = @"SELECT     * 
            //                    FROM    pal_vLoyaltyDetail
            //                    WHERE   CAST(dateCreated AS DATE) = '" + DateTime.Now.ToShortDateString() + "'";
            string query = @"select * from pal_vLoyaltyDetail where cast(dateCreated AS DATE) = cast(getdate() as date)";
            command = CreateCommand(_dbName, query);
            returnString += "|query:" + query;
            DataSet ds = dataTier.ReturnMethods.ReturnDataSet(ref command);
            if (ds.Tables.Count > 0)
                dtLoyaltyDetail = ds.Tables[0];

            if (dtLoyaltyDetail.Rows.Count > 0)
            {
                dtLoyaltyDetail.Columns.Remove("recId");
                utils.SetDataTableColumnOrder(ref dtLoyaltyDetail, new string[] { "storeCode","storeName","groupCode","groupName",
                "name","surname","idNumber","mobileNo","email","loyaltyNumber","optIn","isActive","dateCreated","dateUpdated"});

                var path = Assembly.GetExecutingAssembly().Location;
                var directory = Path.GetDirectoryName(path) + "\\upload\\" + _dbName + "\\temp";
                utils.validateFolder(directory);
                var fullPath = directory + "\\loyalty.xlsx";
                if (File.Exists(fullPath))
                    File.Delete(fullPath);
                utils.ExportDataTabletoExcel(dtLoyaltyDetail, fullPath);
                returnString += "|FilePath: "+fullPath;
                oEmail mail = new oEmail();
                mail.Attachments.Add(fullPath);
                mail.toAddress = recipients;
                mail.Subject = "Today's New Loyalty Members";

                communication.SendAnEmail(mail);
            }

            returnString += "|success";
        }
        catch (Exception ex)
        {
            returnString +="|"+ ex.Message;
            //exception.HandleException("loyalty class:", MethodBase.GetCurrentMethod().Name, ex, 0, true);
        }
        return returnString;
    }

    /// <summary>
    /// Create a SQL Commane for a database name
    /// </summary>
    /// <param name="dbName"></param>
    /// <returns></returns>
    public static SqlCommand CreateCommand(string dbName, string query)
    {
        string conn = handler.ReturnConfigAppSetting("conn");
        conn = conn.Replace("{catalog}", dbName);
        SqlConnection con = new SqlConnection(conn);
        con.Open();
        SqlCommand result = new SqlCommand(query, con);
        return result;
    }
}