using Ease.Core.DataAccess;
using HRM.BO;
using Microsoft.Data.SqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using static iTextSharp.text.pdf.AcroFields;

namespace HRM.DA
{
    class LoanApplicationDA
    {
        #region Get
        internal static IDataReader Get(TransactionContext tc, int EmpID)
        {
            return tc.ExecuteReader("SELECT * FROM LoanApplication Where EmployeeID = %n", EmpID);
        }

        internal static DataSet GetLoanApplicationData(TransactionContext tc, int EmpID)//serialID
        {
            DataSet rootDataset = new DataSet();
            DataSet tempdataset = new DataSet();
            try
            {
                string query = SQLParser.MakeSQL(@"SELECT * FROM LoanApplication Where EmployeeID=%n", EmpID);

                tempdataset = tc.ExecuteDataSet(query);

                tempdataset.Tables[0].TableName = "FundEssDataSet_LoanApplication";
                rootDataset.Tables.Add(tempdataset.Tables[0].Copy());
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            return rootDataset;
        }

        internal static IDataReader GetAll(TransactionContext tc)
        {
            return tc.ExecuteReader("SELECT * FROM LoanApplication");
        }
        #endregion

        #region Insert

        internal static void Insert(TransactionContext tc, LoanApplication loanApp)
        {
            string sql = SQLParser.MakeSQL(
                @"INSERT INTO LoanApplication(LoanAppID, LoanAppNO, ApplyDate, MemberID, EmployeeID, LoanAmount, NoOfInstallment, InstallmentAmount, Principal, Interest, purposeID, Remarks, FundJoining, BasicSalary, GrossSalary, PreviouslyTakenLoan, CreatedDate, CreatedBy, AccountUserID, AccountAppDate, AccountsRemarks, HRUserID, HRApproveDate, HRApproveRemarks, TrusteesID, TrusteesApproveDate, TrusteesApproveRemarks)" +
                " VALUES(%n, %n, %d, %n, %n, %n, %n, %n, %n, %n, %n, %s, %d, %n, %n, %n, %d, %n, %n, %d, %s, %n, %d, %s, %n, %d, %s)",
                loanApp.LoanAppID, loanApp.LoanAppNO, loanApp.ApplyDate, loanApp.MemberID, loanApp.EmployeeID,
                loanApp.LoanAmount, loanApp.NoOfInstallment, loanApp.InstallmentAmount, loanApp.Principal, loanApp.Interest,
                loanApp.purposeID, loanApp.Remarks, loanApp.FundJoining, loanApp.BasicSalary, loanApp.GrossSalary,
                loanApp.PreviouslyTakenLoan, loanApp.CreatedDate, loanApp.CreatedBy, loanApp.AccountUserID,
                loanApp.AccountAppDate, loanApp.AccountsRemarks, loanApp.HRUserID, loanApp.HRApproveDate,
                loanApp.HRApproveRemarks, loanApp.TrusteesID, loanApp.TrusteesApproveDate, loanApp.TrusteesApproveRemarks);

            tc.ExecuteNonQuery(sql);
        }

        internal static void InsertLoanAttachment(LoanAttachment item, string conn)
        {
            //string sql = SQLParser.MakeSQL(
            //    @"INSERT INTO LoanAttachment(LoanAttachmentID, LoanAppID, LoanDocType, FileName, FileData, uploadDate, uploadBy)" +
            //    " VALUES(%n, %n, %n, %s, %s, %d, %n)",
            //    attachment.LoanAttachmentID, attachment.LoanAppID, attachment.LoanDocType, attachment.FileName,
            //    attachment.FileData, attachment.uploadDate, attachment.uploadBy);

            //tc.ExecuteNonQuery(sql);

            using (SqlConnection connection = new SqlConnection(conn))
            {
                connection.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = connection;
                string commandText = @"INSERT INTO LoanAttachment(LoanAttachmentID, LoanAppID, LoanDocType, FileName, FileData, uploadDate, uploadBy)
                                    Values (@LoanAttachmentID,@LoanAppID,@LoanDocType,@FileName,@FileData,@uploadDate,@uploadBy)";
                cmd.CommandText = commandText;
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.Add("@LoanAttachmentID", SqlDbType.Int);
                cmd.Parameters["@LoanAttachmentID"].Value = item.LoanAttachmentID;

                cmd.Parameters.Add("@LoanAppID", SqlDbType.Int);
                cmd.Parameters["@LoanAppID"].Value = item.LoanAppID;

                cmd.Parameters.Add("@LoanDocType", SqlDbType.SmallInt);
                cmd.Parameters["@LoanDocType"].Value = (int)item.LoanDocType;

                cmd.Parameters.Add("@FileName", SqlDbType.VarChar);
                cmd.Parameters["@FileName"].Value = item.FileName;

                cmd.Parameters.Add("@FileData", SqlDbType.VarBinary);
                cmd.Parameters["@FileData"].Value = item.FileData;

                cmd.Parameters.Add("@uploadDate", SqlDbType.DateTime);
                cmd.Parameters["@uploadDate"].Value = item.uploadDate;

                cmd.Parameters.Add("@uploadBy", SqlDbType.Int);
                cmd.Parameters["@uploadBy"].Value = item.uploadBy;

                cmd.ExecuteNonQuery();
                cmd.Dispose();

                connection.Close();
            }
        }

        #endregion

        #region Update

        internal static void Update(TransactionContext tc, LoanApplication loanApp)
        {
            string sql = SQLParser.MakeSQL(
                @"UPDATE LoanApplication SET LoanAppNO = %n, ApplyDate = %d, MemberID = %n, EmployeeID = %n, LoanAmount = %n, 
                NoOfInstallment = %n, InstallmentAmount = %n, Principal = %n, Interest = %n, purposeID = %n, Remarks = %s, 
                FundJoining = %d, BasicSalary = %n, GrossSalary = %n, PreviouslyTakenLoan = %b, CreatedDate = %d, 
                CreatedBy = %n, AccountUserID = %n, AccountAppDate = %d, AccountsRemarks = %s, HRUserID = %n, HRApproveDate = %d, 
                HRApproveRemarks = %s, TrusteesID = %n, TrusteesApproveDate = %d, TrusteesApproveRemarks = %s WHERE LoanAppID = %n",
                loanApp.LoanAppNO, loanApp.ApplyDate, loanApp.MemberID, loanApp.EmployeeID, loanApp.LoanAmount,
                loanApp.NoOfInstallment, loanApp.InstallmentAmount, loanApp.Principal, loanApp.Interest, loanApp.purposeID,
                loanApp.Remarks, loanApp.FundJoining, loanApp.BasicSalary, loanApp.GrossSalary, loanApp.PreviouslyTakenLoan,
                loanApp.CreatedDate, loanApp.CreatedBy, loanApp.AccountUserID, loanApp.AccountAppDate, loanApp.AccountsRemarks,
                loanApp.HRUserID, loanApp.HRApproveDate, loanApp.HRApproveRemarks, loanApp.TrusteesID,
                loanApp.TrusteesApproveDate, loanApp.TrusteesApproveRemarks, loanApp.LoanAppID);

            tc.ExecuteNonQuery(sql);
        }


        #endregion

        #region Delete
        internal static void Delete(TransactionContext tc, int iD)
        {
            tc.ExecuteNonQuery("Delete From LoanApplication Where LoanAppID=%n", iD);
        }

        internal static void DeleteLoanAttachments(TransactionContext tc, int iD)
        {
            tc.ExecuteNonQuery("Delete From LoanAttachment Where LoanAppID=%n", iD);
        }

        #endregion
    }
}