using System;
using System.Data;
using System.Collections.Generic;
using System.Text;
using DSWeb.MvcShipping.Models.MsCwGenlegAccitems;
using DSWeb.MvcShipping.Models.MsCwAccitemsGl;
using Microsoft.Practices.EnterpriseLibrary.Data;
using DSWeb.EntityDA;
using DSWeb.Areas.CommMng.Models;
using HcUtility.Comm;
using System.Data.SqlClient;
using DSWeb.DataAccess;
using HcUtility.Core;
using DSWeb.Areas.CommMng.DAL;

namespace DSWeb.MvcShipping.DAL.RptCwGLDAL
{
    public class RptCwGLDAL
    {
        #region 查询
        static public List<MsCwGenlegAccitems> GetDataList(string strCondition, string ACCDATEbgn, string ACCDATEend, string strCbHSKM, string strCURRENCY, string userid, string usercode, string companyid, string sort = null)
        {
            var strSql = new StringBuilder();
            string sSql = GetDataListSQL(strCondition, ACCDATEbgn, ACCDATEend, strCbHSKM, strCURRENCY, userid, usercode, companyid);
            strSql.Append(sSql);
            //
            var sortstring = DatasetSort.Getsortstring(sort);
            if (!string.IsNullOrEmpty(sortstring))
            {
                strSql.Append(" order by " + sortstring);
            }
            else
            {
                strSql.Append(" order by LINKCODE,LINKNAME,ACCDATE,EXPLAN2");
            }
            return SetData(strSql);
        }

        static public string GetDataListStr(string strCondition, string ACCDATEbgn, string ACCDATEend, string strCbHSKM, string strCURRENCY, string userid, string usercode, string companyid)
        {
            var strSql = new StringBuilder();
            string sSql = GetDataListSQL(strCondition, ACCDATEbgn, ACCDATEend, strCbHSKM, strCURRENCY, userid, usercode, companyid);
            strSql.Append(sSql);
            strSql.Append(" order by LINKCODE,LINKNAME,ACCDATE,EXPLAN2");
            return strSql.ToString();
        }

        static public string GetDataListSQL(string strCondition, string ACCDATEbgn, string ACCDATEend, string strCbHSKM, string strCURRENCY, string userid, string usercode, string companyid)
        {
            string strCwSTARTGID = BasicDataRefDAL.GetCwSTARTGID(userid);
            var strSql = new StringBuilder();
            if (strCURRENCY.Trim() == "综合本位币")
            {
                #region 综合本位币
                strSql.Append("(SELECT newid() as GID,LINKCODE,ACCDATE,'年初余额' as EXPLAN,1 as EXPLAN2,DC");
                if (strCbHSKM.IndexOf("CORPACC") > -1)
                {
                    strSql.Append(",(LINKNAME+(case when (CORPACC='' or CORPACC is null) then '' else ('▁'+CORPACC) end)+(case when (DEPTACC='' or DEPTACC is null) then '' else ('▁'+DEPTACC) end)+(case when (EMPLACC='' or EMPLACC is null) then '' else ('▁'+EMPLACC) end)+(case when (ITEMACC='' or ITEMACC is null) then '' else ('▁'+ITEMACC) end)) as LINKNAME,DEPTACC,EMPLACC,CORPACC,ITEMACC");
                }
                else
                {
                    strSql.Append(",LINKNAME,'' as DEPTACC,'' as EMPLACC,'' as CORPACC,'' as ITEMACC");
                }
                strSql.Append(",isnull(sum(QTYYEARDR),0) as DR,isnull(sum(QTYYEARCR),0) as CR,isnull(sum(QTYYEARBLC),0) as BLC");
                strSql.Append(" from (");
                strSql.Append("select *,LINKCODE=(select top 1 ACCID from [cw_accitems_gl] where gid=cw_genleg_accitems.LINKGID),LINKNAME=(select top 1 ACCNAME from [cw_accitems_gl] where gid=cw_genleg_accitems.LINKGID),DC=(select top 1 DC from [cw_accitems_gl] where gid=cw_genleg_accitems.LINKGID) from cw_genleg_accitems where [STARTGID]='" + strCwSTARTGID + "' and ISDELETE=0 and ISENABLE=1 " + strCbHSKM + ACCDATEbgn);
                if (!string.IsNullOrEmpty(strCondition))
                {
                    strSql.Append(" and " + strCondition);
                }
                //strSql.Append(" and CORPID='" + companyid + "'");
                strSql.Append(") as a");
                if (strCbHSKM.IndexOf("CORPACC") > -1)
                {
                    strSql.Append(" group by LINKGID,LINKCODE,LINKNAME,ACCDATE,DC,DEPTACC,EMPLACC,CORPACC,ITEMACC)");
                }
                else
                {
                    strSql.Append(" group by LINKGID,LINKCODE,LINKNAME,ACCDATE,DC)");
                }
                strSql.Append(" union ");
                strSql.Append("(SELECT newid() as GID,LINKCODE,ACCDATE,'本期合计' as EXPLAN,2 as EXPLAN2,DC");
                if (strCbHSKM.IndexOf("CORPACC") > -1)
                {
                    strSql.Append(",(LINKNAME+(case when (CORPACC='' or CORPACC is null) then '' else ('▁'+CORPACC) end)+(case when (DEPTACC='' or DEPTACC is null) then '' else ('▁'+DEPTACC) end)+(case when (EMPLACC='' or EMPLACC is null) then '' else ('▁'+EMPLACC) end)+(case when (ITEMACC='' or ITEMACC is null) then '' else ('▁'+ITEMACC) end)) as LINKNAME,DEPTACC,EMPLACC,CORPACC,ITEMACC");
                }
                else
                {
                    strSql.Append(",LINKNAME,'' as DEPTACC,'' as EMPLACC,'' as CORPACC,'' as ITEMACC");
                }
                strSql.Append(",isnull(sum(QTYDR),0) as DR,isnull(sum(QTYCR),0) as CR,isnull(sum(QTYBLC),0) as BLC");
                strSql.Append(" from (");
                strSql.Append("select *,LINKCODE=(select top 1 ACCID from [cw_accitems_gl] where gid=cw_genleg_accitems.LINKGID),LINKNAME=(select top 1 ACCNAME from [cw_accitems_gl] where gid=cw_genleg_accitems.LINKGID),DC=(select top 1 DC from [cw_accitems_gl] where gid=cw_genleg_accitems.LINKGID) from cw_genleg_accitems where [STARTGID]='" + strCwSTARTGID + "' and ISDELETE=0 and ISENABLE=1 " + strCbHSKM);
                if (!string.IsNullOrEmpty(strCondition))
                {
                    strSql.Append(" and " + strCondition);
                }
                //strSql.Append(" and CORPID='" + companyid + "'");
                strSql.Append(") as a");
                if (strCbHSKM.IndexOf("CORPACC") > -1)
                {
                    strSql.Append(" group by LINKGID,LINKCODE,LINKNAME,ACCDATE,DC,DEPTACC,EMPLACC,CORPACC,ITEMACC)");
                }
                else
                {
                    strSql.Append(" group by LINKGID,LINKCODE,LINKNAME,ACCDATE,DC)");
                }
                strSql.Append(" union ");
                strSql.Append("(SELECT newid() as GID,LINKCODE,ACCDATE,'本年累计' as EXPLAN,3 as EXPLAN2,DC");
                if (strCbHSKM.IndexOf("CORPACC") > -1)
                {
                    strSql.Append(",(LINKNAME+(case when (CORPACC='' or CORPACC is null) then '' else ('▁'+CORPACC) end)+(case when (DEPTACC='' or DEPTACC is null) then '' else ('▁'+DEPTACC) end)+(case when (EMPLACC='' or EMPLACC is null) then '' else ('▁'+EMPLACC) end)+(case when (ITEMACC='' or ITEMACC is null) then '' else ('▁'+ITEMACC) end)) as LINKNAME,DEPTACC,EMPLACC,CORPACC,ITEMACC");
                }
                else
                {
                    strSql.Append(",LINKNAME,'' as DEPTACC,'' as EMPLACC,'' as CORPACC,'' as ITEMACC");
                }
                strSql.Append(",isnull(sum(QTYLASTDR),0) as DR,isnull(sum(QTYLASTCR),0) as CR,isnull(sum(QTYLASTBLC),0) as BLC");
                strSql.Append(" from (");
                strSql.Append("select *,LINKCODE=(select top 1 ACCID from [cw_accitems_gl] where gid=cw_genleg_accitems.LINKGID),LINKNAME=(select top 1 ACCNAME from [cw_accitems_gl] where gid=cw_genleg_accitems.LINKGID),DC=(select top 1 DC from [cw_accitems_gl] where gid=cw_genleg_accitems.LINKGID) from cw_genleg_accitems where [STARTGID]='" + strCwSTARTGID + "' and ISDELETE=0 and ISENABLE=1 " + strCbHSKM);
                if (!string.IsNullOrEmpty(strCondition))
                {
                    strSql.Append(" and " + strCondition);
                }
                //strSql.Append(" and CORPID='" + companyid + "'");
                strSql.Append(") as a");
                if (strCbHSKM.IndexOf("CORPACC") > -1)
                {
                    strSql.Append(" group by LINKGID,LINKCODE,LINKNAME,ACCDATE,DC,DEPTACC,EMPLACC,CORPACC,ITEMACC)");
                }
                else
                {
                    strSql.Append(" group by LINKGID,LINKCODE,LINKNAME,ACCDATE,DC)");
                }
                #endregion
            }
            else if (strCURRENCY.Trim() == "RMB")
            {
                #region RMB
                strSql.Append("(SELECT newid() as GID,LINKCODE,ACCDATE,'年初余额' as EXPLAN,1 as EXPLAN2,DC");
                if (strCbHSKM.IndexOf("CORPACC") > -1)
                {
                    strSql.Append(",(LINKNAME+(case when (CORPACC='' or CORPACC is null) then '' else ('▁'+CORPACC) end)+(case when (DEPTACC='' or DEPTACC is null) then '' else ('▁'+DEPTACC) end)+(case when (EMPLACC='' or EMPLACC is null) then '' else ('▁'+EMPLACC) end)+(case when (ITEMACC='' or ITEMACC is null) then '' else ('▁'+ITEMACC) end)) as LINKNAME,DEPTACC,EMPLACC,CORPACC,ITEMACC");
                }
                else
                {
                    strSql.Append(",LINKNAME,'' as DEPTACC,'' as EMPLACC,'' as CORPACC,'' as ITEMACC");
                }
                strSql.Append(",isnull(sum(AMTYEARDR),0) as DR,isnull(sum(AMTYEARCR),0) as CR,isnull(sum(AMTYEARBLC),0) as BLC");
                strSql.Append(" from (");
                strSql.Append("select *,LINKCODE=(select top 1 ACCID from [cw_accitems_gl] where gid=cw_genleg_accitems.LINKGID),LINKNAME=(select top 1 ACCNAME from [cw_accitems_gl] where gid=cw_genleg_accitems.LINKGID),DC=(select top 1 DC from [cw_accitems_gl] where gid=cw_genleg_accitems.LINKGID) from cw_genleg_accitems where [STARTGID]='" + strCwSTARTGID + "' and ISDELETE=0 and ISENABLE=1 and CURRENCY='" + strCURRENCY + "' " + strCbHSKM + ACCDATEbgn);
                if (!string.IsNullOrEmpty(strCondition))
                {
                    strSql.Append(" and " + strCondition);
                }
                //strSql.Append(" and CORPID='" + companyid + "'");
                strSql.Append(") as a");
                if (strCbHSKM.IndexOf("CORPACC") > -1)
                {
                    strSql.Append(" group by LINKGID,LINKCODE,LINKNAME,ACCDATE,DC,DEPTACC,EMPLACC,CORPACC,ITEMACC)");
                }
                else
                {
                    strSql.Append(" group by LINKGID,LINKCODE,LINKNAME,ACCDATE,DC)");
                }
                strSql.Append(" union ");
                strSql.Append("(SELECT newid() as GID,LINKCODE,ACCDATE,'本期合计' as EXPLAN,2 as EXPLAN2,DC");
                if (strCbHSKM.IndexOf("CORPACC") > -1)
                {
                    strSql.Append(",(LINKNAME+(case when (CORPACC='' or CORPACC is null) then '' else ('▁'+CORPACC) end)+(case when (DEPTACC='' or DEPTACC is null) then '' else ('▁'+DEPTACC) end)+(case when (EMPLACC='' or EMPLACC is null) then '' else ('▁'+EMPLACC) end)+(case when (ITEMACC='' or ITEMACC is null) then '' else ('▁'+ITEMACC) end)) as LINKNAME,DEPTACC,EMPLACC,CORPACC,ITEMACC");
                }
                else
                {
                    strSql.Append(",LINKNAME,'' as DEPTACC,'' as EMPLACC,'' as CORPACC,'' as ITEMACC");
                }
                strSql.Append(",isnull(sum(AMTDR),0) as DR,isnull(sum(AMTCR),0) as CR,isnull(sum(AMTBLC),0) as BLC");
                strSql.Append(" from (");
                strSql.Append("select *,LINKCODE=(select top 1 ACCID from [cw_accitems_gl] where gid=cw_genleg_accitems.LINKGID),LINKNAME=(select top 1 ACCNAME from [cw_accitems_gl] where gid=cw_genleg_accitems.LINKGID),DC=(select top 1 DC from [cw_accitems_gl] where gid=cw_genleg_accitems.LINKGID) from cw_genleg_accitems where [STARTGID]='" + strCwSTARTGID + "' and ISDELETE=0 and ISENABLE=1 and CURRENCY='" + strCURRENCY + "' " + strCbHSKM);
                if (!string.IsNullOrEmpty(strCondition))
                {
                    strSql.Append(" and " + strCondition);
                }
                //strSql.Append(" and CORPID='" + companyid + "'");
                strSql.Append(") as a");
                if (strCbHSKM.IndexOf("CORPACC") > -1)
                {
                    strSql.Append(" group by LINKGID,LINKCODE,LINKNAME,ACCDATE,DC,DEPTACC,EMPLACC,CORPACC,ITEMACC)");
                }
                else
                {
                    strSql.Append(" group by LINKGID,LINKCODE,LINKNAME,ACCDATE,DC)");
                }
                strSql.Append(" union ");
                strSql.Append("(SELECT newid() as GID,LINKCODE,ACCDATE,'本年累计' as EXPLAN,3 as EXPLAN2,DC");
                if (strCbHSKM.IndexOf("CORPACC") > -1)
                {
                    strSql.Append(",(LINKNAME+(case when (CORPACC='' or CORPACC is null) then '' else ('▁'+CORPACC) end)+(case when (DEPTACC='' or DEPTACC is null) then '' else ('▁'+DEPTACC) end)+(case when (EMPLACC='' or EMPLACC is null) then '' else ('▁'+EMPLACC) end)+(case when (ITEMACC='' or ITEMACC is null) then '' else ('▁'+ITEMACC) end)) as LINKNAME,DEPTACC,EMPLACC,CORPACC,ITEMACC");
                }
                else
                {
                    strSql.Append(",LINKNAME,'' as DEPTACC,'' as EMPLACC,'' as CORPACC,'' as ITEMACC");
                }
                strSql.Append(",isnull(sum(AMTLASTDR),0) as DR,isnull(sum(AMTLASTCR),0) as CR,isnull(sum(AMTLASTBLC),0) as BLC");
                strSql.Append(" from (");
                strSql.Append("select *,LINKCODE=(select top 1 ACCID from [cw_accitems_gl] where gid=cw_genleg_accitems.LINKGID),LINKNAME=(select top 1 ACCNAME from [cw_accitems_gl] where gid=cw_genleg_accitems.LINKGID),DC=(select top 1 DC from [cw_accitems_gl] where gid=cw_genleg_accitems.LINKGID) from cw_genleg_accitems where [STARTGID]='" + strCwSTARTGID + "' and ISDELETE=0 and ISENABLE=1 and CURRENCY='" + strCURRENCY + "' " + strCbHSKM);
                if (!string.IsNullOrEmpty(strCondition))
                {
                    strSql.Append(" and " + strCondition);
                }
                //strSql.Append(" and CORPID='" + companyid + "'");
                strSql.Append(") as a");
                if (strCbHSKM.IndexOf("CORPACC") > -1)
                {
                    strSql.Append(" group by LINKGID,LINKCODE,LINKNAME,ACCDATE,DC,DEPTACC,EMPLACC,CORPACC,ITEMACC)");
                }
                else
                {
                    strSql.Append(" group by LINKGID,LINKCODE,LINKNAME,ACCDATE,DC)");
                }
                #endregion
            }
            else//外币
            {
                #region 外币
                strSql.Append("(SELECT newid() as GID,LINKCODE,ACCDATE,'年初余额' as EXPLAN,1 as EXPLAN2,DC");
                if (strCbHSKM.IndexOf("CORPACC") > -1)
                {
                    strSql.Append(",(LINKNAME+(case when (CORPACC='' or CORPACC is null) then '' else ('▁'+CORPACC) end)+(case when (DEPTACC='' or DEPTACC is null) then '' else ('▁'+DEPTACC) end)+(case when (EMPLACC='' or EMPLACC is null) then '' else ('▁'+EMPLACC) end)+(case when (ITEMACC='' or ITEMACC is null) then '' else ('▁'+ITEMACC) end)) as LINKNAME,DEPTACC,EMPLACC,CORPACC,ITEMACC");
                }
                else
                {
                    strSql.Append(",LINKNAME,'' as DEPTACC,'' as EMPLACC,'' as CORPACC,'' as ITEMACC");
                }
                strSql.Append(",isnull(sum(FCYYEARDR),0) as DR,isnull(sum(FCYYEARCR),0) as CR,isnull(sum(FCYYEARBLC),0) as BLC");
                strSql.Append(" from (");
                strSql.Append("select *,LINKCODE=(select top 1 ACCID from [cw_accitems_gl] where gid=cw_genleg_accitems.LINKGID),LINKNAME=(select top 1 ACCNAME from [cw_accitems_gl] where gid=cw_genleg_accitems.LINKGID),DC=(select top 1 DC from [cw_accitems_gl] where gid=cw_genleg_accitems.LINKGID) from cw_genleg_accitems where [STARTGID]='" + strCwSTARTGID + "' and ISDELETE=0 and ISENABLE=1 and CURRENCY='" + strCURRENCY + "'" + strCbHSKM + ACCDATEbgn);
                if (!string.IsNullOrEmpty(strCondition))
                {
                    strSql.Append(" and " + strCondition);
                }
                //strSql.Append(" and CORPID='" + companyid + "'");
                strSql.Append(") as a");
                if (strCbHSKM.IndexOf("CORPACC") > -1)
                {
                    strSql.Append(" group by LINKGID,LINKCODE,LINKNAME,ACCDATE,DC,DEPTACC,EMPLACC,CORPACC,ITEMACC)");
                }
                else
                {
                    strSql.Append(" group by LINKGID,LINKCODE,LINKNAME,ACCDATE,DC)");
                }
                strSql.Append(" union ");
                strSql.Append("(SELECT newid() as GID,LINKCODE,ACCDATE,'本期合计' as EXPLAN,2 as EXPLAN2,DC");
                if (strCbHSKM.IndexOf("CORPACC") > -1)
                {
                    strSql.Append(",(LINKNAME+(case when (CORPACC='' or CORPACC is null) then '' else ('▁'+CORPACC) end)+(case when (DEPTACC='' or DEPTACC is null) then '' else ('▁'+DEPTACC) end)+(case when (EMPLACC='' or EMPLACC is null) then '' else ('▁'+EMPLACC) end)+(case when (ITEMACC='' or ITEMACC is null) then '' else ('▁'+ITEMACC) end)) as LINKNAME,DEPTACC,EMPLACC,CORPACC,ITEMACC");
                }
                else
                {
                    strSql.Append(",LINKNAME,'' as DEPTACC,'' as EMPLACC,'' as CORPACC,'' as ITEMACC");
                }
                strSql.Append(",isnull(sum(FCYDR),0) as DR,isnull(sum(FCYCR),0) as CR,isnull(sum(FCYBLC),0) as BLC");
                strSql.Append(" from (");
                strSql.Append("select *,LINKCODE=(select top 1 ACCID from [cw_accitems_gl] where gid=cw_genleg_accitems.LINKGID),LINKNAME=(select top 1 ACCNAME from [cw_accitems_gl] where gid=cw_genleg_accitems.LINKGID),DC=(select top 1 DC from [cw_accitems_gl] where gid=cw_genleg_accitems.LINKGID) from cw_genleg_accitems where [STARTGID]='" + strCwSTARTGID + "' and ISDELETE=0 and ISENABLE=1 and CURRENCY='" + strCURRENCY + "' " + strCbHSKM);
                if (!string.IsNullOrEmpty(strCondition))
                {
                    strSql.Append(" and " + strCondition);
                }
                //strSql.Append(" and CORPID='" + companyid + "'");
                strSql.Append(") as a");
                if (strCbHSKM.IndexOf("CORPACC") > -1)
                {
                    strSql.Append(" group by LINKGID,LINKCODE,LINKNAME,ACCDATE,DC,DEPTACC,EMPLACC,CORPACC,ITEMACC)");
                }
                else
                {
                    strSql.Append(" group by LINKGID,LINKCODE,LINKNAME,ACCDATE,DC)");
                }
                strSql.Append(" union ");
                strSql.Append("(SELECT newid() as GID,LINKCODE,ACCDATE,'本年累计' as EXPLAN,3 as EXPLAN2,DC");
                if (strCbHSKM.IndexOf("CORPACC") > -1)
                {
                    strSql.Append(",(LINKNAME+(case when (CORPACC='' or CORPACC is null) then '' else ('▁'+CORPACC) end)+(case when (DEPTACC='' or DEPTACC is null) then '' else ('▁'+DEPTACC) end)+(case when (EMPLACC='' or EMPLACC is null) then '' else ('▁'+EMPLACC) end)+(case when (ITEMACC='' or ITEMACC is null) then '' else ('▁'+ITEMACC) end)) as LINKNAME,DEPTACC,EMPLACC,CORPACC,ITEMACC");
                }
                else
                {
                    strSql.Append(",LINKNAME,'' as DEPTACC,'' as EMPLACC,'' as CORPACC,'' as ITEMACC");
                }
                strSql.Append(",isnull(sum(FCYLASTDR),0) as DR,isnull(sum(FCYLASTCR),0) as CR,isnull(sum(FCYLASTBLC),0) as BLC");
                strSql.Append(" from (");
                strSql.Append("select *,LINKCODE=(select top 1 ACCID from [cw_accitems_gl] where gid=cw_genleg_accitems.LINKGID),LINKNAME=(select top 1 ACCNAME from [cw_accitems_gl] where gid=cw_genleg_accitems.LINKGID),DC=(select top 1 DC from [cw_accitems_gl] where gid=cw_genleg_accitems.LINKGID) from cw_genleg_accitems where [STARTGID]='" + strCwSTARTGID + "' and ISDELETE=0 and ISENABLE=1 and CURRENCY='" + strCURRENCY + "' " + strCbHSKM);
                if (!string.IsNullOrEmpty(strCondition))
                {
                    strSql.Append(" and " + strCondition);
                }
                //strSql.Append(" and CORPID='" + companyid + "'");
                strSql.Append(") as a");
                if (strCbHSKM.IndexOf("CORPACC") > -1)
                {
                    strSql.Append(" group by LINKGID,LINKCODE,LINKNAME,ACCDATE,DC,DEPTACC,EMPLACC,CORPACC,ITEMACC)");
                }
                else
                {
                    strSql.Append(" group by LINKGID,LINKCODE,LINKNAME,ACCDATE,DC)");
                }
                #endregion
            }
            return strSql.ToString();
        }

        private static List<MsCwGenlegAccitems> SetData(StringBuilder strSql)
        {
            var headList = new List<MsCwGenlegAccitems>();
            Database db = DatabaseFactory.CreateDatabase();
            using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
            {
                while (reader.Read())
                {
                    MsCwGenlegAccitems data = new MsCwGenlegAccitems();
                    #region Set DB data to Object
                    data.GID = (reader["GID"] == null ? "" : Convert.ToString(reader["GID"]));//唯一编码
                    //data.YEAR = (reader["YEAR"] == null ? "" : Convert.ToString(reader["YEAR"]));//年
                    //data.MONTH = (reader["MONTH"] == null ? "" : Convert.ToString(reader["MONTH"]));//月
                    //data.LINKGID = (reader["LINKGID"] == null ? "" : Convert.ToString(reader["LINKGID"]));//科目GID
                    //data.CURRENCY = (reader["CURRENCY"] == null ? "RMB" : Convert.ToString(reader["CURRENCY"]));//是否核算外币
                    data.DEPTACC = (reader["DEPTACC"] == null ? "" : Convert.ToString(reader["DEPTACC"]));//是否核算部门
                    data.EMPLACC = (reader["EMPLACC"] == null ? "" : Convert.ToString(reader["EMPLACC"]));//是否核算人员
                    data.CORPACC = (reader["CORPACC"] == null ? "" : Convert.ToString(reader["CORPACC"]));//是否算客户门
                    data.ITEMACC = (reader["ITEMACC"] == null ? "" : Convert.ToString(reader["ITEMACC"]));//是否核算项目
                    data.DR = (reader["DR"] == null ? 0 : Convert.ToDecimal(reader["DR"]));//借方
                    data.CR = (reader["CR"] == null ? 0 : Convert.ToDecimal(reader["CR"]));//贷方
                    data.BLC = (reader["BLC"] == null ? 0 : Convert.ToDecimal(reader["BLC"]));//余额
                    //data.CORPID = (reader["CORPID"] == null ? "" : Convert.ToString(reader["CORPID"]));//分公司代码
                    data.LINKNAME = (reader["LINKNAME"] == null ? "" : Convert.ToString(reader["LINKNAME"]));//科目名称
                    data.LINKCODE = (reader["LINKCODE"] == null ? "" : Convert.ToString(reader["LINKCODE"]));
                    data.ACCDATE = (reader["ACCDATE"] == null ? "" : Convert.ToString(reader["ACCDATE"]));//会计期间
                    data.DC = (reader["DC"] == null ? "" : Convert.ToString(reader["DC"]));
                    data.EXPLAN = (reader["EXPLAN"] == null ? "" : Convert.ToString(reader["EXPLAN"]));
                    #endregion
                    headList.Add(data);
                }
                reader.Close();
            }
            return headList;
        }
        
        /*
        public static void SaveRow(DataRow myRow, string strUSERID, string strCOMPANYID)
        {
            var isPost = true;
            string strACCDATE = myRow["ACCDATE"].ToString().Trim();
            string strCwSTARTGID = BasicDataRefDAL.GetCwSTARTGID(strUSERID);
            string PACCGID = myRow["PACCGID"].ToString().Trim();
            T_ALL_DA T_ALL_DA = new EntityDA.T_ALL_DA();
            DateTime rq = DateTime.Parse(strACCDATE+"-01");
            DateTime rq2 = DateTime.Parse(T_ALL_DA.GetStrSQL("rq", "select getdate() as rq"));
            //
            if (myRow["PACCGID"].ToString().Trim() != "0" && myRow["PACCGID"].ToString().Trim() != "ZC" && myRow["PACCGID"].ToString().Trim() != "FZ" && myRow["PACCGID"].ToString().Trim() != "GT" && myRow["PACCGID"].ToString().Trim() != "QY" && myRow["PACCGID"].ToString().Trim() != "CB" && myRow["PACCGID"].ToString().Trim() != "SY")
            {
                string sSQL = "SELECT GID,ACCID,ACCNAME,DETAILED,DC,ISFCY,ISDEPTACC,ISEMPLACC,ISCORPACC,ISITEMACC,REMARKS,[YEAR],[MONTH],PACCGID=(case when (PACCGID='ZC' or PACCGID='FZ' or PACCGID='GT' or PACCGID='QY' or PACCGID='CB' or PACCGID='SY') then '0' else PACCGID end),ACCATTRIBUTE,ISENABLE,ACCTYPE,PACCID=(select top 1 ACCID from [cw_accitems_gl] as a where [STARTGID]='" + strCwSTARTGID + "' and a.gid=cw_accitems_gl.PACCGID),PACCNAME=(select top 1 ACCNAME from [cw_accitems_gl] as b where [STARTGID]='" + strCwSTARTGID + "' and b.gid=cw_accitems_gl.PACCGID),gid as [id],ACCID+' '+ACCNAME as [NAME],0 as DR,0 as CR from [cw_accitems_gl] WITH(NOLOCK) where [YEAR]=SUBSTRING('" + strACCDATE + "',1,4) and [STARTGID]='" + strCwSTARTGID + "' order by [YEAR],ACCID";
                DataSet ds = T_ALL_DA.GetAllSQL(sSQL);
                if (ds != null)
                {
                    if (ds.Tables[0].Rows.Count > 0)
                    {
                        while (PACCGID.Trim() != "")
                        {
                            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                            {
                                if (ds.Tables[0].Rows[i]["GID"].ToString().Trim() == PACCGID)
                                {
                                    string pDC = ds.Tables[0].Rows[i]["DC"].ToString().Trim();
                                    MsCwGenlegAccitems headRow = new MsCwGenlegAccitems();
                                    string PACCGID_gid = T_ALL_DA.GetStrSQL("gid", "select top 1 isnull(gid,'') as gid from cw_genleg_accitems WITH(NOLOCK) where [STARTGID]='" + strCwSTARTGID + "' and ACCDATE=SUBSTRING(convert(varchar,'" + strACCDATE + "',23),1,7) and [YEAR]=SUBSTRING('" + strACCDATE + "',1,4) and LINKGID='" + PACCGID.Trim() + "' and DEPTACC='' and EMPLACC='' and CORPACC='' and ITEMACC=''");
                                    if (PACCGID_gid.Trim() != "")//"edit"
                                    {
                                        sSQL = "SELECT sum(AMTYEARDR) as AMTYEARDR,sum(AMTYEARCR) as AMTYEARCR,sum(case when '" + pDC + "'=DC then AMTYEARBLC else (-AMTYEARBLC) end) as AMTYEARBLC,sum(AMTDR) as AMTDR,sum(AMTCR) as AMTCR,sum(case when '" + pDC + "'=DC then AMTBLC else (-AMTBLC) end) as AMTBLC,sum(AMTLASTDR) as AMTLASTDR,sum(AMTLASTCR) as AMTLASTCR,sum(case when '" + pDC + "'=DC then AMTLASTBLC else (-AMTLASTBLC) end) as AMTLASTBLC,sum(FCYYEARDR) as FCYYEARDR,sum(FCYYEARCR) as FCYYEARCR,sum(case when '" + pDC + "'=DC then FCYYEARBLC else (-FCYYEARBLC) end) as FCYYEARBLC,sum(FCYDR) as FCYDR,sum(FCYCR) as FCYCR,sum(case when '" + pDC + "'=DC then FCYBLC else (-FCYBLC) end) as FCYBLC,sum(FCYLASTDR) as FCYLASTDR,sum(FCYLASTCR) as FCYLASTCR,sum(case when '" + pDC + "'=DC then FCYLASTBLC else (-FCYLASTBLC) end) as FCYLASTBLC,sum(QTYYEARDR) as QTYYEARDR,sum(QTYYEARCR) as QTYYEARCR,sum(case when '" + pDC + "'=DC then QTYYEARBLC else (-QTYYEARBLC) end) as QTYYEARBLC,sum(QTYDR) as QTYDR,sum(QTYCR) as QTYCR,sum(case when '" + pDC + "'=DC then QTYBLC else (-QTYBLC) end) as QTYBLC,sum(QTYLASTDR) as QTYLASTDR,sum(QTYLASTCR) as QTYLASTCR,sum(case when '" + pDC + "'=DC then QTYLASTBLC else (-QTYLASTBLC) end) as QTYLASTBLC,sum(PFADR) as PFADR,sum(PFACR) as PFACR from [vw_cw_genleg_accitems_gl] WITH(NOLOCK) where [STARTGID]='" + strCwSTARTGID + "' and ACCDATE=SUBSTRING(convert(varchar,'" + strACCDATE + "',23),1,7) and LINKGID in(select gid from cw_accitems_gl where PACCGID='" + PACCGID.Trim() + "' and [YEAR]=SUBSTRING('" + strACCDATE + "',1,4) and [STARTGID]='" + strCwSTARTGID + "')";
                                        DataSet dsLS = T_ALL_DA.GetAllSQL(sSQL);
                                        if (dsLS != null)
                                        {
                                            if (dsLS.Tables[0].Rows.Count > 0)
                                            {
                                                #region 默认值
                                                headRow.YEAR = rq.Year.ToString();//年
                                                headRow.MONTH = rq.Month.ToString();//月
                                                if (rq.Month.ToString().Length == 1)
                                                {
                                                    headRow.MONTH = "0" + rq.Month.ToString();//月
                                                }
                                                headRow.ACCDATE = headRow.YEAR.ToString() + "-" + headRow.MONTH.ToString();//月
                                                headRow.LINKGID = PACCGID.Trim();//科目GID
                                                headRow.DEPTACC = "";//核算部门
                                                headRow.EMPLACC = "";//核算人员
                                                headRow.CORPACC = "";//核算客户
                                                headRow.ITEMACC = "";//核算项目
                                                if (headRow.FCYDR.ToString().Trim() != "0" || headRow.FCYCR.ToString().Trim() != "0")
                                                {
                                                    headRow.CURRENCY = "USD";//币别
                                                }
                                                else
                                                {
                                                    headRow.CURRENCY = "RMB";//币别
                                                }
                                                headRow.AMTYEARDR = Decimal.Parse(dsLS.Tables[0].Rows[0]["AMTYEARDR"].ToString().Trim());//RMB期初借方
                                                headRow.AMTYEARCR = Decimal.Parse(dsLS.Tables[0].Rows[0]["AMTYEARCR"].ToString().Trim());//RMB期初贷方
                                                headRow.AMTYEARBLC = Decimal.Parse(dsLS.Tables[0].Rows[0]["AMTYEARBLC"].ToString().Trim());//RMB期初余额
                                                headRow.AMTDR = Decimal.Parse(dsLS.Tables[0].Rows[0]["AMTDR"].ToString().Trim());//RMB本月借方
                                                headRow.AMTCR = Decimal.Parse(dsLS.Tables[0].Rows[0]["AMTCR"].ToString().Trim());//RMB本月贷方
                                                headRow.AMTBLC = Decimal.Parse(dsLS.Tables[0].Rows[0]["AMTBLC"].ToString().Trim());//RMB本月余额
                                                headRow.AMTLASTDR = Decimal.Parse(dsLS.Tables[0].Rows[0]["AMTLASTDR"].ToString().Trim());//RMB累计借方
                                                headRow.AMTLASTCR = Decimal.Parse(dsLS.Tables[0].Rows[0]["AMTLASTCR"].ToString().Trim());//RMB累计贷方
                                                headRow.AMTLASTBLC = Decimal.Parse(dsLS.Tables[0].Rows[0]["AMTLASTBLC"].ToString().Trim());//RMB累计余额
                                                headRow.FCYYEARDR = Decimal.Parse(dsLS.Tables[0].Rows[0]["FCYYEARDR"].ToString().Trim());//外币期初借方
                                                headRow.FCYYEARCR = Decimal.Parse(dsLS.Tables[0].Rows[0]["FCYYEARCR"].ToString().Trim());//外币期初贷方
                                                headRow.FCYYEARBLC = Decimal.Parse(dsLS.Tables[0].Rows[0]["FCYYEARBLC"].ToString().Trim());//外币期初余额
                                                headRow.FCYDR = Decimal.Parse(dsLS.Tables[0].Rows[0]["FCYDR"].ToString().Trim());//外币本月借方
                                                headRow.FCYCR = Decimal.Parse(dsLS.Tables[0].Rows[0]["FCYCR"].ToString().Trim());//外币本月贷方
                                                headRow.FCYBLC = Decimal.Parse(dsLS.Tables[0].Rows[0]["FCYBLC"].ToString().Trim());//外币本月余额
                                                headRow.FCYLASTDR = Decimal.Parse(dsLS.Tables[0].Rows[0]["FCYLASTDR"].ToString().Trim());//外币累计借方
                                                headRow.FCYLASTCR = Decimal.Parse(dsLS.Tables[0].Rows[0]["FCYLASTCR"].ToString().Trim());//外币累计贷方
                                                headRow.FCYLASTBLC = Decimal.Parse(dsLS.Tables[0].Rows[0]["FCYLASTBLC"].ToString().Trim());//外币累计余额
                                                headRow.QTYYEARDR = Decimal.Parse(dsLS.Tables[0].Rows[0]["QTYYEARDR"].ToString().Trim());//期初借方
                                                headRow.QTYYEARCR = Decimal.Parse(dsLS.Tables[0].Rows[0]["QTYYEARCR"].ToString().Trim());//期初贷方
                                                headRow.QTYYEARBLC = Decimal.Parse(dsLS.Tables[0].Rows[0]["QTYYEARBLC"].ToString().Trim());//期初余额
                                                headRow.QTYDR = Decimal.Parse(dsLS.Tables[0].Rows[0]["QTYDR"].ToString().Trim());//本月借方
                                                headRow.QTYCR = Decimal.Parse(dsLS.Tables[0].Rows[0]["QTYCR"].ToString().Trim());//本月贷方
                                                headRow.QTYBLC = Decimal.Parse(dsLS.Tables[0].Rows[0]["QTYBLC"].ToString().Trim());//本月余额
                                                headRow.QTYLASTDR = Decimal.Parse(dsLS.Tables[0].Rows[0]["QTYLASTDR"].ToString().Trim());//累计借方
                                                headRow.QTYLASTCR = Decimal.Parse(dsLS.Tables[0].Rows[0]["QTYLASTCR"].ToString().Trim());//累计贷方
                                                headRow.QTYLASTBLC = Decimal.Parse(dsLS.Tables[0].Rows[0]["QTYLASTBLC"].ToString().Trim());//累计余额
                                                headRow.PFADR = Decimal.Parse(dsLS.Tables[0].Rows[0]["PFADR"].ToString().Trim());//损益类科目本年累计数借方
                                                headRow.PFACR = Decimal.Parse(dsLS.Tables[0].Rows[0]["PFACR"].ToString().Trim());//损益类科目本年累计数贷方
                                                headRow.CORPID = strCOMPANYID;//分公司代码
                                                headRow.ISDELETE = false;//是否删除
                                                headRow.DELETEUSER = "";//删除人
                                                headRow.DELETETIME = rq2;//删除时间
                                                headRow.CREATEUSER = strUSERID;//创建人gid
                                                headRow.CREATETIME = rq2;//创建时间
                                                headRow.MODIFIEDUSER = strUSERID;//更改操作人gid
                                                headRow.MODIFIEDTIME = rq2;//更改操作时间
                                                #endregion
                                                //headRow.CREATEUSER2 = strUSERID;
                                                //headRow.CORPID2 = strCOMPANYID;
                                                headRow.GID = PACCGID_gid.Trim();//唯一编码
                                                headRow.DbOperationType = DbOperationType.DbotUpd;
                                                headRow.ModelUIStatus = "E";
                                            }
                                        }
                                    }
                                    //
                                    if (isPost)
                                    {
                                        if (headRow != null)
                                        {
                                            var modb = new ModelObjectDB();
                                            DBResult result = modb.Save(headRow);
                                        }
                                    }
                                    //
                                    if (ds.Tables[0].Rows[i]["PACCGID"].ToString().Trim() == "0" || ds.Tables[0].Rows[i]["PACCGID"].ToString().Trim() == "ZC" || ds.Tables[0].Rows[i]["PACCGID"].ToString().Trim() == "FZ" || ds.Tables[0].Rows[i]["PACCGID"].ToString().Trim() == "GT" || ds.Tables[0].Rows[i]["PACCGID"].ToString().Trim() == "QY" || ds.Tables[0].Rows[i]["PACCGID"].ToString().Trim() == "CB" || ds.Tables[0].Rows[i]["PACCGID"].ToString().Trim() == "SY")
                                    {
                                        PACCGID = "";
                                    }
                                    else
                                    {
                                        PACCGID = ds.Tables[0].Rows[i]["PACCGID"].ToString().Trim();
                                    }
                                    break;
                                }
                            }
                        }
                    }
                }
            }
        }
        */
        #endregion

        #region 币别_下拉框
        static public List<CodeCurrencyModel> GetCodeCurrencyList()
        {
            String strSql = "select * from [code_currency]";
            return SetCodeCurrencyData(strSql);
        }
        private static List<CodeCurrencyModel> SetCodeCurrencyData(String strSql)
        {
            var headList = new List<CodeCurrencyModel>();
            Database db = DatabaseFactory.CreateDatabase();
            using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql))
            {
                while (reader.Read())
                {
                    CodeCurrencyModel data = new CodeCurrencyModel();
                    #region Set DB data to Object
                    data.CODENAME = Convert.ToString(reader["CODENAME"]);
                    #endregion
                    headList.Add(data);
                }
                reader.Close();
            }
            //
            CodeCurrencyModel data2 = new CodeCurrencyModel();
            data2.CODENAME = "综合本位币";
            headList.Add(data2);
            return headList;
        }
        #endregion
    }
}