using System;
using System.Data;
using System.Collections.Generic;
using System.Text;
using DSWeb.MvcShipping.DAL.RptCtGenlegAccitemsDAL;
using DSWeb.MvcShipping.DAL.MsCtInitialBalanceDAL;
using DSWeb.MvcShipping.Models.RptCtGenlegAccitems;
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.RptCtGenlegAccitemsMonthDAL
{
    public class RptCtGenlegAccitemsMonthDAL
    {
        #region 查询
        static public List<RptCtGenlegAccitems> GetDataList(string ACCDATEbgn, string ACCDATEend, string sACCID, string sCURRENCY, string userid, string usercode, string companyid, string sort = null)
        {
            var strSql = new StringBuilder();
            string sSql = GetDataListSQL(ACCDATEbgn, ACCDATEend, sACCID, sCURRENCY, 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");
            }
            return SetData(strSql);
        }
        
        private static List<RptCtGenlegAccitems> SetData(StringBuilder strSql)
        {
            var headList = new List<RptCtGenlegAccitems>();
            Database db = DatabaseFactory.CreateDatabase();
            using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
            {
                while (reader.Read())
                {
                    RptCtGenlegAccitems data = new RptCtGenlegAccitems();
                    #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.DAY = (reader["DAY"] == null ? "" : Convert.ToString(reader["DAY"]));//日
                    data.LINKGID = (reader["LINKGID"] == null ? "" : Convert.ToString(reader["LINKGID"]));//科目GID(cw_accitems)
                    data.CURRENCY = (reader["CURRENCY"] == null ? "" : Convert.ToString(reader["CURRENCY"]));//币别
                    data.AMTYEARDR = (reader["AMTYEARDR"] == null ? 0 : Convert.ToDecimal(reader["AMTYEARDR"]));//RMB年初借方
                    data.AMTYEARCR = (reader["AMTYEARCR"] == null ? 0 : Convert.ToDecimal(reader["AMTYEARCR"]));//RMB年初贷方
                    data.AMTYEARBLC = (reader["AMTYEARBLC"] == null ? 0 : Convert.ToDecimal(reader["AMTYEARBLC"]));//RMB年初余额
                    data.AMTDR = (reader["AMTDR"] == null ? 0 : Convert.ToDecimal(reader["AMTDR"]));//RMB本月借方
                    data.AMTCR = (reader["AMTCR"] == null ? 0 : Convert.ToDecimal(reader["AMTCR"]));//RMB本月贷方
                    data.AMTBLC = (reader["AMTBLC"] == null ? 0 : Convert.ToDecimal(reader["AMTBLC"]));//RMB本月余额
                    data.AMTLASTDR = (reader["AMTLASTDR"] == null ? 0 : Convert.ToDecimal(reader["AMTLASTDR"]));//RMB累计借方
                    data.AMTLASTCR = (reader["AMTLASTCR"] == null ? 0 : Convert.ToDecimal(reader["AMTLASTCR"]));//RMB累计贷方
                    data.AMTLASTBLC = (reader["AMTLASTBLC"] == null ? 0 : Convert.ToDecimal(reader["AMTLASTBLC"]));//RMB累计余额
                    data.FCYYEARDR = (reader["FCYYEARDR"] == null ? 0 : Convert.ToDecimal(reader["FCYYEARDR"]));//USD年初借方
                    data.FCYYEARCR = (reader["FCYYEARCR"] == null ? 0 : Convert.ToDecimal(reader["FCYYEARCR"]));//USD年初贷方
                    data.FCYYEARBLC = (reader["FCYYEARBLC"] == null ? 0 : Convert.ToDecimal(reader["FCYYEARBLC"]));//USD年初余额
                    data.FCYDR = (reader["FCYDR"] == null ? 0 : Convert.ToDecimal(reader["FCYDR"]));//USD本月借方
                    data.FCYCR = (reader["FCYCR"] == null ? 0 : Convert.ToDecimal(reader["FCYCR"]));//USD本月贷方
                    data.FCYBLC = (reader["FCYBLC"] == null ? 0 : Convert.ToDecimal(reader["FCYBLC"]));//USD本月余额
                    data.FCYLASTDR = (reader["FCYLASTDR"] == null ? 0 : Convert.ToDecimal(reader["FCYLASTDR"]));//外币累计借方
                    data.FCYLASTCR = (reader["FCYLASTCR"] == null ? 0 : Convert.ToDecimal(reader["FCYLASTCR"]));//外币累计贷方
                    data.FCYLASTBLC = (reader["FCYLASTBLC"] == null ? 0 : Convert.ToDecimal(reader["FCYLASTBLC"]));//外币累计余额
                    data.QTYYEARDR = (reader["QTYYEARDR"] == null ? 0 : Convert.ToDecimal(reader["QTYYEARDR"]));//年初借方
                    data.QTYYEARCR = (reader["QTYYEARCR"] == null ? 0 : Convert.ToDecimal(reader["QTYYEARCR"]));//年初贷方
                    data.QTYYEARBLC = (reader["QTYYEARBLC"] == null ? 0 : Convert.ToDecimal(reader["QTYYEARBLC"]));//年初余额
                    data.QTYDR = (reader["QTYDR"] == null ? 0 : Convert.ToDecimal(reader["QTYDR"]));//本月借方
                    data.QTYCR = (reader["QTYCR"] == null ? 0 : Convert.ToDecimal(reader["QTYCR"]));//本月贷方
                    data.QTYBLC = (reader["QTYBLC"] == null ? 0 : Convert.ToDecimal(reader["QTYBLC"]));//本月余额
                    data.QTYLASTDR = (reader["QTYLASTDR"] == null ? 0 : Convert.ToDecimal(reader["QTYLASTDR"]));//累计借方
                    data.QTYLASTCR = (reader["QTYLASTCR"] == null ? 0 : Convert.ToDecimal(reader["QTYLASTCR"]));//累计贷方
                    data.QTYLASTBLC = (reader["QTYLASTBLC"] == null ? 0 : Convert.ToDecimal(reader["QTYLASTBLC"]));//累计余额
                    //data.IsInitialEntry = (reader["IsInitialEntry"] == null ? false : Convert.ToBoolean(reader["IsInitialEntry"]));//是否期初录入
                    //data.CORPID = (reader["CORPID"] == null ? "" : Convert.ToString(reader["CORPID"]));//分公司代码
                    //data.CREATEUSER = (reader["CREATEUSER"] == null ? "" : Convert.ToString(reader["CREATEUSER"]));//创建人gid
                    //data.CREATETIME = (reader["CREATETIME"] == null ? DateTime.Now : Convert.ToDateTime(reader["CREATETIME"]));//创建时间
                    //data.MODIFIEDUSER = (reader["MODIFIEDUSER"] == null ? "" : Convert.ToString(reader["MODIFIEDUSER"]));//最后一次更新操作人GID
                    //data.MODIFIEDTIME = (reader["MODIFIEDTIME"] == null ? DateTime.Now : Convert.ToDateTime(reader["MODIFIEDTIME"]));//最后一次更改时间
                    //data.ACCDATE = (reader["ACCDATE"] == null ? "" : Convert.ToString(reader["ACCDATE"]));//会计期间
                    //data.STARTGID = (reader["STARTGID"] == null ? "" : Convert.ToString(reader["STARTGID"]));//账套启用GID
                    
                    data.LINKNAME = (reader["LINKNAME"] == null ? "" : Convert.ToString(reader["LINKNAME"]));//科目名称
                    data.LINKCODE = (reader["LINKCODE"] == null ? "" : Convert.ToString(reader["LINKCODE"]));
                    data.DC = (reader["DC"] == null ? "" : Convert.ToString(reader["DC"]));//余额方向
                    data.DC2 = (reader["DC2"] == null ? "" : Convert.ToString(reader["DC2"]));//余额方向
                    data.iDR = (reader["iDR"] == null ? "" : Convert.ToString(reader["iDR"]));//余额方向
                    data.iCR = (reader["iCR"] == null ? "" : Convert.ToString(reader["iCR"]));//余额方向
                    #endregion
                    headList.Add(data);
                }
                reader.Close();
            }
            return headList;
        }

        static public string GetDataListStr(string ACCDATEbgn, string ACCDATEend, string sACCID, string sCURRENCY, string sTYPE, string userid, string usercode, string companyid)
        {
            var strSql = new StringBuilder();
            string sSql = GetDataListSQL(ACCDATEbgn, ACCDATEend, sACCID, sCURRENCY, userid, usercode, companyid);
            if (sCURRENCY == "RMB")
            {
                strSql.Append("select LINKCODE as 科目代码,LINKNAME as 科目名称,DC as 方向,QTYYEARBLC as 期初余额,QTYDR as 本期借方,QTYCR as 本期贷方,DC2 as 方向,QTYLASTBLC as 期末余额,iDR as 借方笔数,iCR as 贷方笔数 from (" + sSql + ") as ls");
            }
            else
            {
                strSql.Append("select LINKCODE as [科目代码],LINKNAME as [科目名称],DC as 方向,FCYYEARBLC as [期初余额（原币）],QTYYEARBLC as [期初余额（本位币）],FCYDR as [本期借方（原币）],QTYDR as [本期借方（本位币）],FCYCR as [本期贷方（原币）],QTYCR as [本期贷方（本位币）],DC2 as [方向],FCYLASTBLC as [期末余额（原币）],QTYLASTBLC as [期末余额（本位币）],iDR as [借方笔数],iCR as [贷方笔数] from (" + sSql + ") as ls");
            }
            if (sTYPE != "export")
            {
                strSql.Append(" order by LINKCODE");
            }
            return strSql.ToString();
        }

        static public string GetDataListSQL(string ACCDATEbgn, string ACCDATEend, string sACCID, string sCURRENCY, string userid, string usercode, string companyid)
        {
            #region 基本信息
            string strCwSTARTGID = BasicDataRefDAL.GetCwSTARTGID(userid);
            string strCtSTARTGID = BasicDataRefDAL.GetCtSTARTGID(userid);
            String strCondition = "";
            String strCondition2 = "";
            String strCondition3 = "";
            if (!string.IsNullOrEmpty(ACCDATEbgn))
            {
                strCondition += " and ([YEAR]+'-'+[MONTH])>='" + ACCDATEbgn + "'";
                strCondition2 += " and ACCDATE>='" + ACCDATEbgn + "'";
            }
            if (!string.IsNullOrEmpty(ACCDATEend))
            {
                strCondition += " and ([YEAR]+'-'+[MONTH])<='" + ACCDATEend + "'";
                strCondition2 += " and ACCDATE<='" + ACCDATEend + "'";
            }
            if (!string.IsNullOrEmpty(sACCID))
            {
                strCondition += " and LINKGID in (select GID from cw_accitems_gl where [YEAR]=SUBSTRING('" + ACCDATEbgn + "',1,4) and [STARTGID]='" + strCwSTARTGID + "' and ACCID='" + sACCID + "')";
                strCondition3 += " and ACCID='" + sACCID + "'";
            }
            if (!string.IsNullOrEmpty(sCURRENCY))
            {
                strCondition += " and CURRENCY='" + sCURRENCY + "'";
                strCondition2 += " and FCYNO='" + sCURRENCY + "'";
            }

            //结束期间的最后一天
            string sVOUDATE = DateTime.Parse(ACCDATEend + "-01").AddMonths(1).AddDays(-1).ToString("yyyy-MM-dd");
            #endregion
            
            #region 判断ct_genleg_accitems是否有期末数据
            T_ALL_DA T_ALL_DA = new EntityDA.T_ALL_DA();
            string sSQL = "select * from cw_accitems_gl where ISBANK=1 and DETAILED=1 and [YEAR]=SUBSTRING('" + ACCDATEend + "',1,4) and [STARTGID]='" + strCwSTARTGID + "' and gid in (select LINKGID from ct_genleg_accitems WITH(NOLOCK) WHERE STARTGID='" + strCtSTARTGID + "' GROUP BY LINKGID) and gid not in (select LINKGID from ct_genleg_accitems WITH(NOLOCK) WHERE STARTGID='" + strCtSTARTGID + "' AND ([YEAR]+'-'+[MONTH]+'-'+[DAY])='" + sVOUDATE + "' and CURRENCY='" + sCURRENCY + "')" + strCondition3;
            DataSet dsCwAccitemsGl = T_ALL_DA.GetAllSQL(sSQL);
            //
            if (dsCwAccitemsGl != null)
            {
                if (dsCwAccitemsGl.Tables[0].Rows.Count > 0)
                {
                    for (int i = 0; i < dsCwAccitemsGl.Tables[0].Rows.Count; i++)
                    {
                        RptCtGenlegAccitemsDAL.RptCtGenlegAccitemsDAL.insertRow(dsCwAccitemsGl.Tables[0].Rows[i]["GID"].ToString(), dsCwAccitemsGl.Tables[0].Rows[i]["DC"].ToString(), sCURRENCY, sVOUDATE, strCtSTARTGID, companyid, userid);

                        #region 本期父类总账表
                        MsCtInitialBalanceDAL.MsCtInitialBalanceDAL.SaveRow(dsCwAccitemsGl.Tables[0].Rows[i]["GID"].ToString(), sCURRENCY, sVOUDATE, strCtSTARTGID, false, companyid, userid);
                        #endregion
                    }
                }
            }
            #endregion

            #region 查询数据
            var strSql = new StringBuilder();
            strSql.Append("SELECT newid() as GID,[LINKGID],[CURRENCY]");
            strSql.Append(",LINKCODE=(select top 1 ACCID from [cw_accitems_gl] where gid=b.LINKGID)");
            strSql.Append(",LINKNAME=(select top 1 ACCNAME from [cw_accitems_gl] where gid=b.LINKGID)");
            strSql.Append(",[AMTYEARDR],[AMTYEARCR],[AMTYEARBLC],[AMTDR],[AMTCR],[AMTBLC],[AMTLASTDR],[AMTLASTCR]");
            strSql.Append(",[AMTLASTBLC]=(case when AMTLASTBLC<0 then (0-AMTLASTBLC) else AMTLASTBLC end)");
            strSql.Append(",[FCYYEARDR],[FCYYEARCR]");
            strSql.Append(",[FCYYEARBLC]=(case when FCYYEARBLC<0 then (0-FCYYEARBLC) else FCYYEARBLC end)");
            strSql.Append(",[FCYDR],[FCYCR],[FCYBLC],[FCYLASTDR],[FCYLASTCR]");
            strSql.Append(",[FCYLASTBLC]=(case when FCYLASTBLC<0 then (0-FCYLASTBLC) else FCYLASTBLC end)");
            strSql.Append(",[QTYYEARDR],[QTYYEARCR]");
            strSql.Append(",[QTYYEARBLC]=(case when QTYYEARBLC<0 then (0-QTYYEARBLC) else QTYYEARBLC end)");
            strSql.Append(",[QTYDR],[QTYCR],[QTYBLC],[QTYLASTDR],[QTYLASTCR]");
            strSql.Append(",[QTYLASTBLC]=(case when QTYLASTBLC<0 then (0-QTYLASTBLC) else QTYLASTBLC end)");
            strSql.Append(",DC=(CASE WHEN QTYYEARBLC<0 THEN '贷' ELSE '借' END)");
            strSql.Append(",DC2=(CASE WHEN QTYLASTBLC<0 THEN '贷' ELSE '借' END)");
            strSql.Append(",iDR=(SELECT COUNT(*) AS inum from ct_bank_journal WHERE STARTGID='" + strCtSTARTGID + "' and DR<>0 AND ACCGID=b.LINKGID and ACCDATE>='" + ACCDATEbgn + "' and ACCDATE<='" + ACCDATEbgn + "' and FCYNO='" + sCURRENCY + "')");
            strSql.Append(",iCR=(SELECT COUNT(*) AS inum from ct_bank_journal WHERE STARTGID='" + strCtSTARTGID + "' and CR<>0 AND ACCGID=b.LINKGID and ACCDATE>='" + ACCDATEbgn + "' and ACCDATE<='" + ACCDATEbgn + "' and FCYNO='" + sCURRENCY + "') ");
            strSql.Append(" from ");
            strSql.Append(" (");
            strSql.Append(" select [LINKGID],[CURRENCY]");
            strSql.Append(",[AMTYEARDR]=(select top 1 [AMTYEARDR] from ct_genleg_accitems WITH(NOLOCK) WHERE STARTGID='" + strCtSTARTGID + "' AND LINKGID=a.LINKGID AND ([YEAR]+'-'+[MONTH])='" + ACCDATEbgn + "' and CURRENCY='" + sCURRENCY + "' ORDER BY [DAY])");
            strSql.Append(",[AMTYEARCR]=(select top 1 [AMTYEARCR] from ct_genleg_accitems WITH(NOLOCK) WHERE STARTGID='" + strCtSTARTGID + "' AND LINKGID=a.LINKGID AND ([YEAR]+'-'+[MONTH])='" + ACCDATEbgn + "' and CURRENCY='" + sCURRENCY + "' ORDER BY [DAY])");
            strSql.Append(",[AMTYEARBLC]=(select top 1 [AMTYEARBLC] from ct_genleg_accitems WITH(NOLOCK) WHERE STARTGID='" + strCtSTARTGID + "' AND LINKGID=a.LINKGID AND ([YEAR]+'-'+[MONTH])='" + ACCDATEbgn + "' and CURRENCY='" + sCURRENCY + "' ORDER BY [DAY])");
            strSql.Append(",ISNULL(sum([AMTDR]),0) as [AMTDR]");
            strSql.Append(",ISNULL(sum([AMTCR]),0) as [AMTCR]");
            strSql.Append(",ISNULL(sum([AMTBLC]),0) as [AMTBLC]");
            strSql.Append(",[AMTLASTDR]=(select top 1 [AMTLASTDR] from ct_genleg_accitems WITH(NOLOCK) WHERE STARTGID='" + strCtSTARTGID + "' AND LINKGID=a.LINKGID AND ([YEAR]+'-'+[MONTH])<='" + ACCDATEend + "' and CURRENCY='" + sCURRENCY + "' ORDER BY [YEAR] DESC,[MONTH] DESC,[DAY] DESC)");
            strSql.Append(",[AMTLASTCR]=(select top 1 [AMTLASTCR] from ct_genleg_accitems WITH(NOLOCK) WHERE STARTGID='" + strCtSTARTGID + "' AND LINKGID=a.LINKGID AND ([YEAR]+'-'+[MONTH])<='" + ACCDATEend + "' and CURRENCY='" + sCURRENCY + "' ORDER BY [YEAR] DESC,[MONTH] DESC,[DAY] DESC)");
            strSql.Append(",[AMTLASTBLC]=(select top 1 [AMTLASTBLC] from ct_genleg_accitems WITH(NOLOCK) WHERE STARTGID='" + strCtSTARTGID + "' AND LINKGID=a.LINKGID AND ([YEAR]+'-'+[MONTH])<='" + ACCDATEend + "' and CURRENCY='" + sCURRENCY + "' ORDER BY [YEAR] DESC,[MONTH] DESC,[DAY] DESC)");
            strSql.Append(",[FCYYEARDR]=(select top 1 [FCYYEARDR] from ct_genleg_accitems WITH(NOLOCK) WHERE STARTGID='" + strCtSTARTGID + "' AND LINKGID=a.LINKGID AND ([YEAR]+'-'+[MONTH])='" + ACCDATEbgn + "' and CURRENCY='" + sCURRENCY + "' ORDER BY [DAY])");
            strSql.Append(",[FCYYEARCR]=(select top 1 [FCYYEARCR] from ct_genleg_accitems WITH(NOLOCK) WHERE STARTGID='" + strCtSTARTGID + "' AND LINKGID=a.LINKGID AND ([YEAR]+'-'+[MONTH])='" + ACCDATEbgn + "' and CURRENCY='" + sCURRENCY + "' ORDER BY [DAY])");
            strSql.Append(",[FCYYEARBLC]=(select top 1 [FCYYEARBLC] from ct_genleg_accitems WITH(NOLOCK) WHERE STARTGID='" + strCtSTARTGID + "' AND LINKGID=a.LINKGID AND ([YEAR]+'-'+[MONTH])='" + ACCDATEbgn + "' and CURRENCY='" + sCURRENCY + "' ORDER BY [DAY])");
            strSql.Append(",ISNULL(sum([FCYDR]),0) as [FCYDR]");
            strSql.Append(",ISNULL(sum([FCYCR]),0) as [FCYCR]");
            strSql.Append(",ISNULL(sum([FCYBLC]),0) as [FCYBLC]");
            strSql.Append(",[FCYLASTDR]=(select top 1 [FCYLASTDR] from ct_genleg_accitems WITH(NOLOCK) WHERE STARTGID='" + strCtSTARTGID + "' AND LINKGID=a.LINKGID AND ([YEAR]+'-'+[MONTH])<='" + ACCDATEend + "' and CURRENCY='" + sCURRENCY + "' ORDER BY [YEAR] DESC,[MONTH] DESC,[DAY] DESC)");
            strSql.Append(",[FCYLASTCR]=(select top 1 [FCYLASTCR] from ct_genleg_accitems WITH(NOLOCK) WHERE STARTGID='" + strCtSTARTGID + "' AND LINKGID=a.LINKGID AND ([YEAR]+'-'+[MONTH])<='" + ACCDATEend + "' and CURRENCY='" + sCURRENCY + "' ORDER BY [YEAR] DESC,[MONTH] DESC,[DAY] DESC)");
            strSql.Append(",[FCYLASTBLC]=(select top 1 [FCYLASTBLC] from ct_genleg_accitems WITH(NOLOCK) WHERE STARTGID='" + strCtSTARTGID + "' AND LINKGID=a.LINKGID AND ([YEAR]+'-'+[MONTH])<='" + ACCDATEend + "' and CURRENCY='" + sCURRENCY + "' ORDER BY [YEAR] DESC,[MONTH] DESC,[DAY] DESC)");
            strSql.Append(",[QTYYEARDR]=(select top 1 [QTYYEARDR] from ct_genleg_accitems WITH(NOLOCK) WHERE STARTGID='" + strCtSTARTGID + "' AND LINKGID=a.LINKGID AND ([YEAR]+'-'+[MONTH])='" + ACCDATEbgn + "' and CURRENCY='" + sCURRENCY + "' ORDER BY [DAY])");
            strSql.Append(",[QTYYEARCR]=(select top 1 [QTYYEARCR] from ct_genleg_accitems WITH(NOLOCK) WHERE STARTGID='" + strCtSTARTGID + "' AND LINKGID=a.LINKGID AND ([YEAR]+'-'+[MONTH])='" + ACCDATEbgn + "' and CURRENCY='" + sCURRENCY + "' ORDER BY [DAY])");
            strSql.Append(",[QTYYEARBLC]=(select top 1 [QTYYEARBLC] from ct_genleg_accitems WITH(NOLOCK) WHERE STARTGID='" + strCtSTARTGID + "' AND LINKGID=a.LINKGID AND ([YEAR]+'-'+[MONTH])='" + ACCDATEbgn + "' and CURRENCY='" + sCURRENCY + "' ORDER BY [DAY])");
            strSql.Append(",ISNULL(sum([QTYDR]),0) as [QTYDR]");
            strSql.Append(",ISNULL(sum([QTYCR]),0) as [QTYCR]");
            strSql.Append(",ISNULL(sum([QTYBLC]),0) as [QTYBLC]");
            strSql.Append(",[QTYLASTDR]=(select top 1 [QTYLASTDR] from ct_genleg_accitems WITH(NOLOCK) WHERE STARTGID='" + strCtSTARTGID + "' AND LINKGID=a.LINKGID AND ([YEAR]+'-'+[MONTH])<='" + ACCDATEend + "' and CURRENCY='" + sCURRENCY + "' ORDER BY [YEAR] DESC,[MONTH] DESC,[DAY] DESC)");
            strSql.Append(",[QTYLASTCR]=(select top 1 [QTYLASTCR] from ct_genleg_accitems WITH(NOLOCK) WHERE STARTGID='" + strCtSTARTGID + "' AND LINKGID=a.LINKGID AND ([YEAR]+'-'+[MONTH])<='" + ACCDATEend + "' and CURRENCY='" + sCURRENCY + "' ORDER BY [YEAR] DESC,[MONTH] DESC,[DAY] DESC)");
            strSql.Append(",[QTYLASTBLC]=(select top 1 [QTYLASTBLC] from ct_genleg_accitems WITH(NOLOCK) WHERE STARTGID='" + strCtSTARTGID + "' AND LINKGID=a.LINKGID AND ([YEAR]+'-'+[MONTH])<='" + ACCDATEend + "' and CURRENCY='" + sCURRENCY + "' ORDER BY [YEAR] DESC,[MONTH] DESC,[DAY] DESC)");
            strSql.Append(" from ct_genleg_accitems as a");
            strSql.Append(" WHERE a.STARTGID='" + strCtSTARTGID + "'" + strCondition);
            //strSql.Append(" and ([YEAR]+'-'+[MONTH])>='" + ACCDATEbgn + "' and ([YEAR]+'-'+[MONTH])<='" + ACCDATEend + "' and CURRENCY='"+sCURRENCY+"'");
            strSql.Append(" GROUP BY [LINKGID],[CURRENCY],[STARTGID]");
            strSql.Append(") as b");
            //strSql.Append(" and CORPID='" + companyid + "'");
            #endregion
            return strSql.ToString();
        }
        #endregion
                
        static public String GetData(string strUserID)
        {
            string strCwACCDATE = BasicDataRefDAL.GetCwACCDATE(strUserID);
            return strCwACCDATE;
        }
    }
}