using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Web;
using System.Web.Mvc;
using DSWeb.Areas.Import.Models.CRBill;
using DSWeb.Areas.Import.Models.CwVouchers;
using DSWeb.Areas.Import.Models.CwVouitems;
using DSWeb.Areas.Import.Models.CwDesign;
using DSWeb.Areas.Import.Models.CWAccitems;
using DSWeb.TruckMng.Helper;
using Microsoft.Practices.EnterpriseLibrary.Data;
using DSWeb.Areas.CommMng.Models;
using HcUtility.Comm;
using HcUtility.Core;
using DSWeb.Areas.Import.DAL.CwVouchersDAL;
using DSWeb.EntityDA;

namespace DSWeb.Areas.Import.Controllers
{
    /// <summary>
    /// 收入成本生成凭证 列表
    /// </summary>
    public class CostRevenueController : Controller
    {


        public ActionResult Index()
        {
            return View();
        }

        public ContentResult GetDataList(int start, int limit, string sort, string condition)
        {
            //2017年4月14日 modify by 张义伟 添加分页功能
            var dataList = GetDataList(condition,sort,start,limit);

           // var list = dataList.Skip(start).Take(limit);
            int count = getTotalCount(condition);
            var json = JsonConvert.Serialize(new { Success = true, Message = "查询成功", totalCount = count, data = dataList.ToList() });
            return new ContentResult() { Content = json };
        }
        //2017年4月14日 modify by 张义伟 添加分页功能
        private static List<CRBill> GetDataList(string strCondition, string sort,int start,int limit)
        {
            var strSql = new StringBuilder();
            strSql.Append(@"SELECT * from (SELECT row_number() over (");
            var sortstring = DatasetSort.Getsortstring(sort);
            if (!string.IsNullOrEmpty(sortstring))
            {
                strSql.Append(" order by " + sortstring);
            }
            else
            {
                strSql.Append(" order by  cf.ENTERDATE");

            }
            strSql.Append(@") as num , ");
            strSql.Append("  cf.[GId],M.[ContractNo],[HTH],[seller],M.[company],[buyer],cf.feename,cf.CUSTOMERNAME,cf.AMOUNT, ");
            strSql.Append(" M.[countryid],c.country,[creator],[MainState],[ShipCompany_id], [BillNo] ,[Vessel],[Voyage], ");
            strSql.Append(" dbo.trimdate([Ex_sailingdate]) [Ex_sailingdate],dbo.trimdate([Sailingdate]) [Sailingdate], ");
            strSql.Append(" dbo.trimdate([ArrivalDate]) [ArrivalDate],dbo.trimdate([creattime]) [creattime], ");
            strSql.Append(" (select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=3 and EnumValueID=M.MainState) as MainStateRef, ");
            strSql.Append(" (select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=10 and EnumValueID=M.port) as portRef, ");
            strSql.Append(" (select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=5 and EnumValueID=M.ShipCompany_id) as ShipCompanyRef, ");
            strSql.Append(" (select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=99024 and EnumValueID=cf.feestatus) as feestatusref, ");
            strSql.Append(" (select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=99020 and EnumValueID=cf.feetype) as feetyperef, ");
            strSql.Append(" M.port, cf.remark,cf.ACCDATE,case cf.ISVOU when 1 then '是' else '否' end as ISVOU ,cf.VOUCHERNO ");
            strSql.Append(" ,dbo.f_danhao(cf.gid,4) [AC_AD],dbo.f_danhao(cf.gid,1)+dbo.f_danhao(cf.gid,8) CR,dbo.f_danhao(cf.gid,2)+dbo.f_danhao(cf.gid,9)  DR  ");
            strSql.Append(" FROM [dbo].[ch_fee] cf ");
            strSql.Append(" left join import_main m on cf.bsno=m.contractno ");
            strSql.Append(" left join code_country c on m.countryid=c.countryid ");

            if (!string.IsNullOrEmpty(strCondition))
            {
                strSql.Append(" where 1=1 and " + strCondition);
            }
            strSql.Append(@")as t ");
            strSql.Append(string.Format("where  t.num>={0} and t.num<={1} ", start, start + limit));

            return SetData(strSql);
        }

        //2017年4月14日 add by 张义伟
        /// <summary>
        /// 获取数据总数
        /// </summary>
        /// <param name="strCondition">查询条件</param>
        /// <returns></returns>
        private static int getTotalCount ( string strCondition)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(*) from [dbo].[ch_fee] cf  left join import_main m on cf.bsno=m.contractno  left join code_country c on m.countryid=c.countryid  ");
            if (!string.IsNullOrEmpty(strCondition))
            {
                strSql.Append(" where 1=1 and " + strCondition);
            }
            int cnt = 0;
            Database db = DatabaseFactory.CreateDatabase();
            using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
            {
                while (reader.Read())
                {
                    cnt = Convert.ToInt32(reader[0]);
                }
            }
            return cnt;
        }

        private static List<CRBill> SetData(StringBuilder strSql)
        {
            var headList = new List<CRBill>();
            Database db = DatabaseFactory.CreateDatabase();
            using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
            {
                while (reader.Read())
                {

                    CRBill data = new CRBill();
                    #region Set DB data to Object
             
                    data.country = Convert.ToString(reader["country"]);
                    data.FEENAME = Convert.ToString(reader["FEENAME"]);
                    data.CUSTOMERNAME = Convert.ToString(reader["CUSTOMERNAME"]);
                    data.FEETYPEREF = Convert.ToString(reader["FEETYPEREF"]);
                    data.FEESTATUSREF = Convert.ToString(reader["FEESTATUSREF"]);
                    data.AMOUNT = Convert.ToString(reader["AMOUNT"]);

                    data.GId = Convert.ToString(reader["GId"]);

                    data.ContractNo = Convert.ToString(reader["ContractNo"]);
                    data.HTH = Convert.ToString(reader["HTH"]);
                    data.seller = Convert.ToString(reader["seller"]);
                    data.buyer = Convert.ToString(reader["buyer"]);
                
                    data.countryid = Convert.ToString(reader["countryid"]);
                    data.creator = Convert.ToString(reader["creator"]);
               
                    data.Mainstate = Convert.ToString(reader["Mainstate"]);
                    data.MainstateRef = Convert.ToString(reader["MainstateRef"]);
                  
                    data.ShipCompany_id = Convert.ToString(reader["ShipCompany_id"]);
                    data.BillNo = Convert.ToString(reader["BillNo"]);
                  
                    data.Vessel = Convert.ToString(reader["Vessel"]);
                    data.Voyage = Convert.ToString(reader["Voyage"]);
                    data.Ex_sailingdate = Convert.ToString(reader["Ex_sailingdate"]);
                    data.Sailingdate = Convert.ToString(reader["Sailingdate"]);
                    data.ArrivalDate = Convert.ToString(reader["ArrivalDate"]);

                    data.creattime = Convert.ToString(reader["creattime"]);
                    data.port = Convert.ToString(reader["port"]);
                    data.remark = Convert.ToString(reader["remark"]);
                    data.portRef = Convert.ToString(reader["portRef"]);
                    data.ShipCompanyRef = Convert.ToString(reader["ShipCompanyRef"]);
            
                    data.ACCDATE = Convert.ToString(reader["ACCDATE"]);
                    //data.ContractStatus = Convert.ToString(reader["ContractStatus"]);
                    data.ISVOU = Convert.ToString(reader["ISVOU"]);
                    data.VOUCHERNO = Convert.ToString(reader["VOUCHERNO"]);
                    data.AC = Convert.ToString(reader["AC_AD"]);
                    //data.AD = Convert.ToString(reader["AD"]);
                    data.CR = Convert.ToString(reader["CR"]);
                    data.DR = Convert.ToString(reader["DR"]);

                    #endregion
                    headList.Add(data);
                }
                reader.Close();
            }
            return headList;
        }


        public ContentResult CreateVoucher(string VoucherDate,string UsdExrate,string User,string useRate, string feesql)
        {

            var modb = new ModelObjectDB();
            DBResult result = CreateDCVoucher(VoucherDate,UsdExrate,User,useRate,feesql);

            var jsonRespose = new JsonResponse { Success = result.Success, Message = result.Message };
            return new ContentResult() { Content = JsonConvert.Serialize(jsonRespose) };

        }

        public static DBResult CreateDCVoucher(string VoucherDate, string UsdExrate, string User, string useRate, string feesql)
        {
            var result = new DBResult();
            result.Success = true; 
            var HTH = "";
            var VouitemsList = new List<CwVouitems>();
            var VouOrdNo = Guid.NewGuid().ToString();
            CwDesign CWDesign = CwVouchersDAL.GetCwDesign();
            if (CWDesign.AR == "" || CWDesign.AR == null || CWDesign.AP == "" || CWDesign.AP == null || CWDesign.ARFC == "" || CWDesign.ARFC == null
               || CWDesign.APFC == "" || CWDesign.APFC == null || CWDesign.MBINCOME == "" || CWDesign.ARFC ==null)
            {
                result.Success = false;
                result.Message = "请先设置帐套信息,才能生成凭证！";
            }
            else
            {
                var DebitAccID = CwVouchersDAL.GetCwAccitems(CWDesign.AR);
                var UsdDebitAccID = CwVouchersDAL.GetCwAccitems(CWDesign.ARFC);
                var CreditAccID = CwVouchersDAL.GetCwAccitems(CWDesign.AP);
                var UsdCreditAccID = CwVouchersDAL.GetCwAccitems(CWDesign.APFC);
                var InComeAccID = CwVouchersDAL.GetCwAccitems(CWDesign.MBINCOME);
                decimal DebitAmt = Convert.ToDecimal(0.00);
                decimal CreditAmt = Convert.ToDecimal(0.00);
                int itemsno = 1;
                CwVouchers Voucher = new CwVouchers();

                Database db = DatabaseFactory.CreateDatabase();
                using (var conn = db.CreateConnection())
                {
                    conn.Open();

                    var strSql = new StringBuilder();
                    if (useRate == "1")
                    {
                        strSql.Append("SELECT m.HTH,FEETYPE");
                        strSql.Append(", FAPCODE=(select top 1 FAPCODE from info_client where SHORTNAME=c.CUSTOMERNAME) ");
                        strSql.Append(", FARCODE=(select top 1 FARCODE from info_client where SHORTNAME=c.CUSTOMERNAME) ");
                        strSql.Append(",c.CUSTOMERNAME,c.EXCHANGERATE,c.CURRENCY,c.ISADVANCEDPAY,SUM(c.AMOUNT) AS je,cast(round(SUM(c.AMOUNT*c.EXCHANGERATE),2) as numeric(20,2)) AS hj");
                        strSql.Append(",c.EXCHANGERATE From ch_fee c left join Import_main m on (m.ContractNo=c.bsno) WHERE c.GId IN " + feesql);
                        strSql.Append(" GROUP BY m.HTH,c.FEETYPE,c.CUSTOMERNAME,c.EXCHANGERATE,c.CURRENCY,c.ISADVANCEDPAY");
                        strSql.Append(" ORDER BY c.FEETYPE desc,c.CURRENCY,c.CUSTOMERNAME");
                    }
                    else
                    {

                        strSql.Append("SELECT m.HTH,FEETYPE");
                        strSql.Append(", FAPCODE=(select top 1 FAPCODE from info_client where SHORTNAME=c.CUSTOMERNAME) ");
                        strSql.Append(", FARCODE=(select top 1 FARCODE from info_client where SHORTNAME=c.CUSTOMERNAME) ");
                        strSql.Append(",c.CUSTOMERNAME,c.EXCHANGERATE,c.CURRENCY,c.ISADVANCEDPAY,SUM(c.AMOUNT) AS je,cast(round(SUM(c.AMOUNT*" + UsdExrate + "),2) as numeric(20,2)) AS hj,");
                        strSql.Append(UsdExrate + " as EXCHANGERATE From ch_fee c left join Import_main m on (m.ContractNo=c.bsno) WHERE c.GId IN " + feesql);
                        strSql.Append(" GROUP BY m.HTH,c.FEETYPE,c.CUSTOMERNAME,c.EXCHANGERATE,c.CURRENCY,c.ISADVANCEDPAY");
                        strSql.Append(" ORDER BY c.FEETYPE desc,c.CURRENCY,c.CUSTOMERNAME");

                    }

                    using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
                    {
                        while (reader.Read())
                        {
                           

                            CwVouitems data = new CwVouitems();
                            HTH = Convert.ToString(reader["HTH"]);
                            if (HTH.Length > 25)
                            {
                                HTH = HTH.Substring(0, 25);
                            }

                            data.ORDNO = VouOrdNo;
                            data.BLNO = HTH;

                            var _explan = "合同号:" + HTH + "  " + Convert.ToString(reader["CUSTOMERNAME"]);
                            data.EXPLAN = _explan;
                            data.ITEMNO = itemsno;
                            data.CUSTOMERNAME = Convert.ToString(reader["CUSTOMERNAME"]);
                            
                            if (Convert.ToString(reader["FEETYPE"]) == "1")
                            {
                                data.ACCID = DebitAccID.ACCID;
                                data.ACCNAME = DebitAccID.ACCNAME;
                                if (DebitAccID.DC == "借")
                                {
                                    data.DC ="D";
                                }
                                else if (DebitAccID.DC == "贷")
                                {
                                    data.DC = "C";
                                }
                                if (Convert.ToString(reader["FARCODE"]) == "")
                                {
                                    result.Success = false;
                                    result.Message = "客户:" + Convert.ToString(reader["CUSTOMERNAME"]) + "没有设置对应的财务代码！";
                                }
                                data.CORPID = Convert.ToString(reader["FARCODE"]);
                              
                                if (Convert.ToString(reader["CURRENCY"]) == "USD")
                                {
                                    data.AMTDR = Convert.ToDecimal(reader["hj"]);
                                    data.AMTCR = Convert.ToDecimal(0.00);
                                    data.FCYCR = Convert.ToDecimal(0.00);
                                    data.FCYDR = Convert.ToDecimal(reader["je"]);
                                    data.FCYEXRATE = Convert.ToDecimal(reader["EXCHANGERATE"]);
                                    data.FCYNO = "USD";
                                    DebitAmt = DebitAmt + Convert.ToDecimal(reader["hj"]);
                                    data.ACCID = UsdDebitAccID.ACCID;
                                    data.ACCNAME = UsdDebitAccID.ACCNAME;
                                } else

                                    if (Convert.ToString(reader["CURRENCY"]) != "USD" && Convert.ToString(reader["CURRENCY"]) != "RMB")
                                {
                                    data.AMTDR = Convert.ToDecimal(reader["hj"]);
                                    data.AMTCR = Convert.ToDecimal(0.00);
                                    data.FCYCR = Convert.ToDecimal(0.00);
                                    data.FCYDR = Convert.ToDecimal(reader["je"]);
                                    data.FCYEXRATE = Convert.ToDecimal(reader["EXCHANGERATE"]);
                                    data.FCYNO = Convert.ToString(reader["CURRENCY"]);
                                    DebitAmt = DebitAmt + Convert.ToDecimal(reader["hj"]);
                                    data.ACCID = UsdDebitAccID.ACCID;
                                    data.ACCNAME = UsdDebitAccID.ACCNAME;
                                }
                                else
                                {
                                    data.AMTDR = Convert.ToDecimal(reader["je"]);
                                    data.AMTCR = Convert.ToDecimal(0.00);
                                    data.FCYCR = Convert.ToDecimal(0.00);
                                    data.FCYDR = Convert.ToDecimal(0.00);
                                    data.FCYEXRATE = Convert.ToDecimal(0);
                                    data.FCYNO = "RMB";
                                    DebitAmt = DebitAmt + Convert.ToDecimal(reader["je"]);

                                }

                            }
                            else if (Convert.ToString(reader["FEETYPE"]) == "2")
                            {
                                data.ACCID = CreditAccID.ACCID;
                                data.ACCNAME = CreditAccID.ACCNAME;
                                data.DC = CreditAccID.DC;
                                if (Convert.ToString(reader["FARCODE"]) == "")
                                {
                                    result.Success = false;
                                    result.Message = "客户:" + Convert.ToString(reader["CUSTOMERNAME"]) + "没有设置对应的财务代码！";
                                }

                                data.CORPID = Convert.ToString(reader["FAPCODE"]);
                                if (Convert.ToString(reader["CURRENCY"]) == "USD")
                                {
                                    data.AMTCR = Convert.ToDecimal(reader["hj"]);
                                    data.AMTDR = Convert.ToDecimal(0.00);
                                    data.FCYDR = Convert.ToDecimal(0.00);
                                    data.FCYCR = Convert.ToDecimal(reader["je"]);
                                    data.FCYEXRATE = Convert.ToDecimal(reader["EXCHANGERATE"]);
                                    data.FCYNO = "USD";
                                    CreditAmt = CreditAmt + Convert.ToDecimal(reader["hj"]);
                                    data.ACCID = UsdCreditAccID.ACCID;
                                    data.ACCNAME = UsdCreditAccID.ACCNAME;
                                }
                                else if (Convert.ToString(reader["CURRENCY"]) != "USD" && Convert.ToString(reader["CURRENCY"]) != "RMB")
                                {
                                    data.AMTCR = Convert.ToDecimal(reader["hj"]);
                                    data.AMTDR = Convert.ToDecimal(0.00);
                                    data.FCYDR = Convert.ToDecimal(0.00);
                                    data.FCYCR = Convert.ToDecimal(reader["je"]);
                                    data.FCYEXRATE = Convert.ToDecimal(reader["EXCHANGERATE"]);
                                    data.FCYNO = Convert.ToString(reader["CURRENCY"]);
                                    CreditAmt = CreditAmt + Convert.ToDecimal(reader["hj"]);
                                    data.ACCID = UsdCreditAccID.ACCID;
                                    data.ACCNAME = UsdCreditAccID.ACCNAME;
                                }
                                else
                                {
                                    data.AMTCR = Convert.ToDecimal(reader["je"]);
                                    data.AMTDR = Convert.ToDecimal(0.00);
                                    data.FCYCR = Convert.ToDecimal(0.00);
                                    data.FCYDR = Convert.ToDecimal(0.00);
                                    data.FCYEXRATE = Convert.ToDecimal(0);
                                    data.FCYNO = "RMB";
                                    CreditAmt = CreditAmt + Convert.ToDecimal(reader["je"]);
                                }

                            }
                            itemsno = itemsno + 1;
                            VouitemsList.Add(data);

                        }
                        reader.Close();

                        if (DebitAmt != CreditAmt)
                        {
                            CwVouitems Incomeitems = new CwVouitems();
                            Incomeitems.ORDNO = VouOrdNo;
                            Incomeitems.BLNO = HTH;
                            Incomeitems.EXPLAN = "合同号：" + HTH;
                            Incomeitems.ITEMNO = itemsno;
                            Incomeitems.ACCID = InComeAccID.ACCID;
                            Incomeitems.ACCNAME = InComeAccID.ACCNAME;
                            Incomeitems.DC = InComeAccID.DC;
                            Incomeitems.AMTCR = Convert.ToDecimal(DebitAmt - CreditAmt);
                            Incomeitems.AMTDR = Convert.ToDecimal(0.00);
                            Incomeitems.FCYCR = Convert.ToDecimal(0.00);
                            Incomeitems.FCYDR = Convert.ToDecimal(0.00);
                            Incomeitems.FCYEXRATE = Convert.ToDecimal(0.00);
                            Incomeitems.FCYNO = "RMB";


                            VouitemsList.Add(Incomeitems);
                        }

                    }


                    Voucher.ORDNO = VouOrdNo;
                    var voudate = Convert.ToDateTime(VoucherDate);
                    Voucher.ACCYEAR = voudate.ToString("yyyy");
                    Voucher.ACCMONTH = voudate.ToString("MM");
                    Voucher.PREPARED = User;
                    Voucher.VOUDATE = voudate;
                    Voucher.VKNO = CWDesign.TVW;
                    if (DebitAmt >= CreditAmt)
                    {
                        Voucher.AMTDR = DebitAmt;
                        Voucher.AMTCR = DebitAmt;
                    }
                    else
                    {
                        Voucher.AMTDR = CreditAmt;
                        Voucher.AMTCR = CreditAmt;
                    }
                //帐套设置
                    T_ALL_DA T_ALL_DA = new T_ALL_DA();
                    DataSet dsCwDesign = T_ALL_DA.GetAllSQL("SELECT top 1 * from cw_design");
                    if (dsCwDesign == null)
                    {
                        result.Success = false;
                        result.Message = "没有设置账套信息！";
                    }

                    Voucher.VOUNO = CwVouchersDAL.getVOUNO(dsCwDesign, Voucher.ACCYEAR, Voucher.ACCMONTH);
                    Voucher.VOUALLNO = Voucher.ACCYEAR + Voucher.ACCMONTH + Voucher.VOUNO;
                    Voucher.ATTACHS = CWDesign.AFFIXNO;
                    Voucher.FCY = "";
                    Voucher.QTY = "";//是否含有数量科目="";//varchar="";//(1)
                    Voucher.VOUPROP = "";//凭证属性="";//varchar="";//(1)
                    Voucher.CHECKED = "";//审核人="";//varchar="";//(10)
                    Voucher.ENTERED = "";//记账人="";//varchar="";//(10)
                    Voucher.ERRMSG = "";//错误信息="";//varchar="";//(6)
                    Voucher.ISDELETE = false;//是否废除="";//bit="";//Y
                    Voucher.DELETEUSER = "";//废除操作人="";//Varchar(36)="";//Y

                }

                var strSel = " AND GId in " + feesql;
                if (result.Success)
                {
                    result = CwVouchersDAL.InsertVoucher(Voucher, VouitemsList, "ch_fee", Voucher.VOUALLNO, strSel, User);
                }
            }
 
  
            return result;

        }


        public ContentResult SetACCDATE(string ACCDATE, string feesql)
        {

            var modb = new ModelObjectDB();
            DBResult result = SetFEEACCDATE(ACCDATE, feesql);

            var jsonRespose = new JsonResponse { Success = result.Success, Message = result.Message };
            return new ContentResult() { Content = JsonConvert.Serialize(jsonRespose) };
        }

        public ContentResult SetAllACCDATE ( string ACCDATE, string condition )
        {

            var modb = new ModelObjectDB();
            DBResult result = CwVouchersDAL.SetAllACCDATE(ACCDATE, "ch_fee", condition);

            var jsonRespose = new JsonResponse { Success = result.Success, Message = result.Message };
            return new ContentResult() { Content = JsonConvert.Serialize(jsonRespose) };
        }

        

        public ContentResult SetBusinessACCDATE ( string ACCDATE, string feesql )
        {

            var modb = new ModelObjectDB();
            DBResult result = SetBusinessFEEACCDATE(ACCDATE, feesql);

            var jsonRespose = new JsonResponse { Success = result.Success, Message = result.Message };
            return new ContentResult() { Content = JsonConvert.Serialize(jsonRespose) };
        }

        public static DBResult SetFEEACCDATE(string ACCDATE, string feesql)
        {
            var strSel = " AND GId in " + feesql;
            var result = CwVouchersDAL.SetFEEACCDATE(ACCDATE, "ch_fee", strSel);

            return result;
        }

        public static DBResult SetBusinessFEEACCDATE ( string ACCDATE, string feesql )
        {
            var strSel = " AND BSNO in " + feesql + " and feestatus in(0,8,9) and isnull(accdate,'')=''";
            var result = CwVouchersDAL.SetFEEACCDATE(ACCDATE, "ch_fee", strSel);

            return result;
        }
    }
}