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

namespace dccdc.DAL
{
    public class invoice_printDal
    {
        public List<invoice_printModel> GetAllList(string id)
        {
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                string param = "";
                if (id != "")
                {
                    param += " and id=@id";
                }

                string sql = "select * from invoice_print where 1=1 ";
                return conn.Query<invoice_printModel>(sql + param, new { id = id }).ToList();
            }
        }

        public List<charge_project_detailModel> GetDetailList(string id)
        {
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                string sql = "select * from charge_project_detail where 1=1 and invoice_print_id=@id";
                return conn.Query<charge_project_detailModel>(sql, new { id = id }).ToList();
            }
        }

        public object GetAllList2(string start, string end, string jfbm, string where,string truename,string hstart,string hend)
        {
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                string param = "";
                if (jfbm != "")
                {
                    param += " and department=@jfbm";
                }
                if (where != "")
                {
                    param += " and (invoice_num like @where or charge_person_name like @where or company like @where or serial_number like @where )";
                }
                if (truename != "")
                {
                    param += " and charge_person_name=@truename";
                }
                if(!string.IsNullOrEmpty(hstart))
                {
                    param += " and invoice_num>=@hstart";
                }
                if (!string.IsNullOrEmpty(hend))
                {
                    param += " and invoice_num<=@hend";
                }
                DateTime dtend=DateTime.Now;
                DateTime dtstart = DateTime.Now;
                if (!string.IsNullOrEmpty(start))
                {
                    // and invoice_date<= @end
                    
                    if(DateTime.TryParse(start,out dtstart))
                    {
                        param += " and invoice_date >= @start";
                    }
                }
                if (!string.IsNullOrEmpty(end))
                {
                    // and invoice_date<= @end
                   
                    if (DateTime.TryParse(end, out dtend))
                    {
                        param += " and invoice_date<= @end";
                    }
                }
                param += " order by id desc";
                string sql = "select * from invoice_print where 1=1 ";
                return conn.Query(sql + param, new { start = dtstart, end = dtend, jfbm = jfbm, where = "%" + where + "%", truename = truename,hstart,hend }).ToList();
            }
        }

        public List<string> GetPerson()
        {
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                string sql = "select distinct charge_person_name from invoice_print where 1=1";
                return conn.Query<string>(sql).ToList();
            }
        }

        public object cancelInvoice(string id, ERPUser user)
        {
            invoice_printModel result;
            charge_maintModel result2;
            List<charge_project_detailModel> models;
            string fph1;
            string fph2;
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                try
                {
                    result = conn.Query<invoice_printModel>("select * from invoice_print where 1=1 and id=@id ", new { id = id }).FirstOrDefault();
                    //发票段表
                    result2 = conn.Query<charge_maintModel>("select * from charge_maint where 1=1 and id=@fpdid", new { fpdid = result.fpdid }).FirstOrDefault();
                    //明细
                    models = conn.Query<charge_project_detailModel>("select * from charge_project_detail where 1=1 and invoice_print_id=@invoice_print_id", new { invoice_print_id = id }).ToList();
                    //创建负发票
                    string fphs = new chargeDal().getInvoice(user.TrueName, result.print_type);
                    if (!fphs.Contains("|"))
                    {
                        return new { State = 0, Message = fphs };
                    }
                    fph1 = fphs.Split('|')[0];
                    fph2 = fphs.Split('|')[1];
                }
                catch (Exception ex)
                {
                    return new { State = 0, Message = ex.Message };
                }
            }


            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                IDbTransaction transaction = conn.BeginTransaction();
                try
                {
                    if (result.invoice_date == DateTime.Now.ToString("yyyy-MM-dd"))
                    {
                        //收费表未打印
                        conn.Execute("update charge set status='作废' where id=@id", new { id = result.chargeid }, transaction);
                        decimal total_money = result2.total_money - result.invoice_price;
                        string normal_cou = (Convert.ToInt32(string.IsNullOrEmpty(result2.normal_cou) ? "0" : result2.normal_cou) - 1).ToString();
                        string abnormal_cou = (Convert.ToInt32(string.IsNullOrEmpty(result2.abnormal_cou) ? "0" : result2.abnormal_cou) + 1).ToString();
                        //发票段表
                        conn.Execute("update charge_maint set total_money=@total_money ,normal_cou=@normal_cou, abnormal_cou=@abnormal_cou where id=@fpdid", new { total_money = total_money, normal_cou = normal_cou, abnormal_cou = abnormal_cou, fpdid = result.fpdid }, transaction);
                        //发票表
                        conn.Execute("update invoice_print set iscancel='-1' where id=@id", new { id = id }, transaction);
                        conn.Execute("update ymdj set jfzt='未缴费' where chargeid=@id", new { id = result.chargeid }, transaction);

                    }
                    else
                    {
                        return new { State = 0, Message = "不能作废非当日发票！" };
                        //收费表未打印
                        conn.Execute("update charge set isprint='未打印' where id=@id", new { id = result.chargeid }, transaction);
                        decimal total_money = result2.total_money - result.invoice_price;
                        string normal_cou = (Convert.ToInt32(string.IsNullOrEmpty(result2.normal_cou) ? "0" : result2.normal_cou) - 1).ToString();
                        string abnormal_cou = (Convert.ToInt32(string.IsNullOrEmpty(result2.abnormal_cou) ? "0" : result2.abnormal_cou) + 1).ToString();
                        //发票段表
                        conn.Execute("update charge_maint set total_money=@total_money ,normal_cou=@normal_cou, abnormal_cou=@abnormal_cou where id=@fpdid", new { total_money = total_money, normal_cou = normal_cou, abnormal_cou = abnormal_cou, fpdid = result.fpdid }, transaction);
                        //发票表
                        conn.Execute("update invoice_print set iscancel='0' where id=@id", new { id = id }, transaction);

                        result.invoice_date = DateTime.Now.ToString("yyyy-MM-dd");
                        result.pay_date = DateTime.Now.ToString("yyyy-MM-dd");
                        result.invoice_num2 = result.invoice_num;
                        result.invoice_num = fph1;
                        result.fpdid = Convert.ToInt32(fph2);
                        result.invoice_price = -result.invoice_price;
                        result.actually_pay = -result.actually_pay;
                        string sql1 = @"INSERT INTO [dbo].[invoice_print]
                                   ([invoice_num],[is_print_price_count],[company] ,[payment_type],[department] ,[charge_person_name],[description],[person_count] ,[match_person_count],[invoice_price],[actually_pay],[invoice_date],[status],[exam_type],[pay_company] ,[pay_date] ,[pay_person],[match_price],[no_match_price],[invoice_type] ,[money_type],[regist_rant],[dept_id],[return_description],[derate_money],[print_type],[accept_status],[serial_number],[upload_status],[clear_num],[chargeid],[fpdid],[invoice_num2],[iscancel])
                             VALUES
                                   (@invoice_num ,@is_print_price_count ,@company ,@payment_type ,@department,@charge_person_name ,@description ,@person_count ,@match_person_count ,@invoice_price ,@actually_pay ,@invoice_date ,@status ,@exam_type ,@pay_company,@pay_date,@pay_person,@match_price ,@no_match_price ,@invoice_type,@money_type ,@regist_rant ,@dept_id,@return_description ,@derate_money
                                   ,@print_type ,@accept_status ,@serial_number ,@upload_status
                                   ,@clear_num,@chargeid,@fpdid,@invoice_num2,@iscancel)select SCOPE_IDENTITY()";
                        int newid =conn.Query<int>(sql1, result, transaction).FirstOrDefault();

                        foreach (charge_project_detailModel model in models)
                        {
                            model.invoice_print_id = newid;
                            model.charge_num = -model.charge_num;
                            model.charge_price = -model.charge_num;
                        }
                        string sql2 = @"INSERT INTO [dbo].[charge_project_detail]
                                   ([invoice_print_id],[charge_project],[unit_price],[charge_num],[charge_price])
                             VALUES
                                   (@invoice_print_id,@charge_project,@unit_price,@charge_num,@charge_price)";
                        conn.Execute(sql2, models, transaction);

                    }
                    transaction.Commit();
                    return new { State = 1, Message = "作废成功！" };
                }
                catch (Exception ex)
                {
                    transaction.Rollback();
                    return new { State = 0, Message = ex.Message };
                }
            }
        }

        public Dictionary<dynamic, dynamic> GetPersonCount(string truename)
        {
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                string sql = "select payment_type,sum(invoice_price) as prices from invoice_print where 1=1 and pay_date='" + DateTime.Now.ToString("yyyy-MM-dd") + "' and charge_person_name=@truename and iscancel=1 group by payment_type";
                return conn.Query(sql, new { truename = truename }).ToDictionary(x => x.payment_type.ToString(), x => x.prices.ToString());
            }
        }

    }
}