using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using dccdc.Models;
using Dapper;

namespace dccdc.DAL
{
    public class OccupationHistoryDal
    {
        public int getCount(string key)
        {
            string sql = "select count(1) from dbo.occupation_history where 1=1";
            if (!string.IsNullOrEmpty(key))
            {
                sql += " and inquiry_id like @inquiry_id";
            }
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                return conn.ExecuteScalar<int>(sql, new { inquiry_id = "%" + key + "%" });
            }
        }

        public List<OccupationHistoryModel> getList(string key)
        {
            //throw new NotImplementedException();
            string sql = "select *,row_number() over(order by id) as rownum from occupation_history where 1=1";
            if (!string.IsNullOrEmpty(key))
            {
                sql += " and inquiry_id = @inquiry_id";
            }
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                return conn.Query<Models.OccupationHistoryModel>(sql, new { inquiry_id = key }).ToList();
            }
        }

        public object save(OccupationHistoryModel cpm, ERPUser user)
        {
            string sql = "";
            if (cpm.id == 0)
            {
                sql = @"INSERT INTO [dbo].[occupation_history]
                     (
                    [inquiry_id],
                    [start_date],
                    [end_date],
                    [util],
                    [workshop],
                    [work_type],
                    [factory],
                    [protect_method]
                    )
                         VALUES
                               (
                    @inquiry_id,
                    @start_date,
                    @end_date,
                    @util,
                    @workshop,
                    @work_type,
                    @factory,
                    @protect_method
                    )
                    ";
            }
            else
            {
                sql = @"UPDATE [dbo].[occupation_history]
                       SET [inquiry_id]=@inquiry_id,
                    [start_date]=@start_date,
                    [end_date]=@end_date,
                    [util]=@util,
                    [workshop]=@workshop,
                    [work_type]=@work_type,
                    [factory]=@factory,
                    [protect_method]=@protect_method
                     WHERE id=@id
                    ";
            }
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                try
                {
                    int c = conn.Execute(sql, cpm);
                    if (c > 0)
                    {
                        return new { State = 1, Message = "保存成功！" };
                    }
                    else
                    {
                        return new { State = 0, Message = "操作失败，请联系管理员！" };
                    }
                }
                catch (Exception ex)
                {
                    return new { State = 0, Message = ex.Message };
                }
            }
        }
        public object delete(string id)
        {
            string sql = "delete from occupation_history where id=@id";

            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                try
                {
                    int result = conn.Execute(sql, new { id = id });
                    if (result > 0)
                    {
                        return new { State = 1, Message = "操作成功！" };
                    }
                    else
                        return new { State = 0, Message = "操作失败！" };
                }
                catch (Exception ex)
                {
                    return new { State = 0, Message = ex.Message };
                }
            }
        }
    }
}