using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using DataLibrary;
using CoreSavingLibrary;
namespace Saving.CriteriaIReport.Share_Paper_year
{
    public partial class Share_Paper_year : PageWebReport, WebReport
    {
        protected String app;
        protected String gid;
        protected String rid;
        public void InitJsPostBack()
        {
            dsMain.InitDsMain(this);
        }

        public void WebSheetLoadBegin()
        {
            //--- Page Arguments
            try
            {
                app = Request["app"].ToString();
            }
            catch { }
            if (app == null || app == "")
            {
                app = state.SsApplication;
            }
            try
            {
                gid = Request["gid"].ToString();
            }
            catch { }
            try
            {
                rid = Request["rid"].ToString();
            }
            catch { }

            //Report Name.
            try
            {
                Sta ta = new Sta(state.SsConnectionString);
                String sql = "";
                sql = @"SELECT REPORT_NAME  
                    FROM WEBREPORTDETAIL  
                    WHERE ( GROUP_ID = '" + gid + @"' ) AND ( REPORT_ID = '" + rid + @"' )";
                Sdt dt = ta.Query(sql);
                ReportName.Text = dt.Rows[0]["REPORT_NAME"].ToString();
                ta.Close();
            }
            catch
            {
                ReportName.Text = "[" + rid + "]";
            }

            if (!IsPostBack)
            {
                //dsMain.DdMembgroup();
                String mbgroup = "";
                Sdt d = WebUtil.QuerySdt(" select membgroup_code from mbucfmembgroup where membgroup_section=1 ");
                while (d.Next())
                {
                    if (d.GetRowIndex() == 1)
                    {
                        mbgroup += d.GetString("membgroup_code").Trim();
                    }
                    else
                    {
                        mbgroup += "," + d.GetString("membgroup_code").Trim();
                    }
                }

                dsMain.DATA[0].membgroup_start = mbgroup;
                dsMain.DdCoopId();
                dsMain.DATA[0].coop_id = state.SsCoopControl;
                dsMain.DATA[0].adtm_sdate = state.SsWorkDate;
                dsMain.DATA[0].adtm_edate = state.SsWorkDate;

            }
        }

        public void CheckJsPostBack(string eventArg)
        {

        }

        public void RunReport()
        {
            string coop_id = dsMain.DATA[0].coop_id;
            DateTime adtm_start = dsMain.DATA[0].adtm_sdate;
            DateTime adtm_end = dsMain.DATA[0].adtm_edate;
          //  string as_sgroup = dsMain.DATA[0].membgroup_start;
            int bfmale = 0, malePlus = 0 , maleRes=0;
            int bffemale = 0, femalePlus = 0, femaleRes = 0;

            Decimal bfsh_amt = 0, sh_amt = 0, sh_swd = 0;
            try
            {
            //string as_egroup = dsMain.DATA[0].membgroup_end;
            string[] loantype = dsMain.DATA[0].membgroup_start.Split(',');  // Split ออกมาใส่ไว้ใน Arrai string
            List<string> last = new List<string>(loantype);      // convert เป็น list  

            string as_sgroup = "";
            for (int i = 0; i < last.Count; i++)
            {
                as_sgroup += string.Format("'{0}'", last[i]);
                if (i != (last.Count - 1))
                {
                    as_sgroup += ",";
                }
            }
                
            int bfmale2 = 0, malePlus2 = 0, maleRes2 = 0;
            int bffemale2 = 0, femalePlus2 = 0, femaleRes2 = 0, femalemaster = 0, malemaster = 0, femalemaster2 = 0, malemaster2 = 0; 

            Decimal bfsh_amt2 = 0, sh_amt2 = 0, sh_swd2 = 0;
            Decimal bfsh_master = 0, sh_master1 = 0, sh_master2 = 0;

                //หายอด คงเหลือ จาก master

            string sql = @"select  sum((sharestk_amt * 10))as sharestk_amt
                            ,count(mbmembmaster.member_no) as count_member
                            ,sum(case when mbmembmaster.sex= 'F' then  1 else 0 end ) as F
                            ,sum(case when mbmembmaster.sex= 'M' then  1 else 0 end ) as M
                            from shsharemaster
                            inner join mbmembmaster on shsharemaster.member_no =mbmembmaster.member_no
                            left join mbucfmembgroup on mbucfmembgroup.membgroup_code= mbmembmaster.membgroup_code
                            where member_date <{1} and resign_status =0  and  sharestk_amt >0 and  mbmembmaster.membgroup_code not in
                            (" + as_sgroup + ") and   mbmembmaster.coop_id='" + state.SsCoopControl + "'";

            sql = WebUtil.SQLFormat(sql, state.SsCoopId, adtm_end);
            Sdt mast_1 =  WebUtil.QuerySdt(sql);

            if (mast_1.Next())
            {
                sh_master1 = mast_1.GetDecimal("sharestk_amt");
                femalemaster = mast_1.GetInt32("F");
                malemaster = mast_1.GetInt32("M");
            }


            string sql2 = @"select  sum((sharestk_amt * 10))as sharestk_amt
                            ,count(member_no) as count_member
                            ,sum(case when sex= 'F' then  1 else 0 end ) as F
                            ,sum(case when sex= 'M' then  1 else 0 end ) as M from (
                            select mbmembmaster.member_no,mbmembmaster.sex,sharestk_amt
                            from shsharemaster
                            inner join mbmembmaster on shsharemaster.member_no =mbmembmaster.member_no
                            left join mbucfmembgroup on mbucfmembgroup.membgroup_code= mbmembmaster.membgroup_code
                            where member_date <{2} and resign_status =0  and  
                              sharestk_amt >0  and  mbmembmaster.membgroup_code  in (" + as_sgroup + ") and   mbmembmaster.coop_id='" + state.SsCoopControl + "'" + @"
                            union 
                            select  mbmembmaster.member_no,mbmembmaster.sex,sharestk_amt
                            from shsharemaster
                            inner join mbmembmaster on shsharemaster.member_no =mbmembmaster.member_no
                            left join mbucfmembgroup on mbucfmembgroup.membgroup_code= mbmembmaster.membgroup_code
                            where  resign_status =1  
                              and  mbmembmaster.membgroup_code  in (" + as_sgroup + ") and   mbmembmaster.coop_id='" + state.SsCoopControl + "'" +@"
                             and   mbmembmaster.coop_id={1} and resign_date >=  {2}
                            )";


            sql2 = WebUtil.SQLFormat(sql2, adtm_start, state.SsCoopId, adtm_start);
            Sdt mast_2 = WebUtil.QuerySdt(sql2);
     
            if (mast_2.Next())
            {
                sh_master2 = mast_2.GetDecimal("sharestk_amt");
                femalemaster2 = mast_2.GetInt32("F");
                malemaster2 = mast_2.GetInt32("M");
            }


     
           // malePlus = GetCountPesonal("(member_date between {0} and {1}) and member_status = 1", adtm_start, adtm_end, "M", as_sgroup, " not in ");
           // maleRes = GetCountPesonal("(resign_date between {0} and {1}) ", adtm_start, adtm_end, "M", as_sgroup, " not in ");

         //   bffemale = GetCountPesonal("(member_date < {0} ) and member_status = 1 ", adtm_start, adtm_end, "F", as_sgroup, " not in ");
         //   femalePlus = GetCountPesonal("(member_date between {0} and {1}) and member_status = 1 ", adtm_start, adtm_end, "F", as_sgroup, " not in "); // ไม่ต้องลบ  แล้ว
            //   femaleRes = GetCountPesonal("(resign_date between {0} and {1}) ", adtm_start, adtm_end, "F", as_sgroup, " not in "); // ไม่ต้องลบ  แล้ว

            sh_amt = GetShareValues(adtm_start, adtm_end, as_sgroup, " not in ");
            sh_swd = GetSWDShareValues(adtm_start, adtm_end, as_sgroup, " not in ");


            decimal sh_amt_2 = GetShareValues2(adtm_start, adtm_end, as_sgroup, " not in ");
            decimal sh_swd_2 = GetSWDShareValues2(adtm_start, adtm_end, as_sgroup, " not in ");
         
            bfmale = (malemaster - malePlus) + maleRes;
            bffemale = (femalemaster - femalePlus) + femaleRes;




           
      
            malePlus2 = GetCountPesonal("(member_date between {0} and {1}) and member_status = 1 ", adtm_start, adtm_end, "M", as_sgroup, " in ");
            maleRes2 = GetCountPesonal("(resign_date between {0} and {1}) ", adtm_start, adtm_end, "M", as_sgroup, " in ");

          //  bffemale2 = GetCountPesonal("(member_date < {0} ) and member_status = 1 ", adtm_start, adtm_end, "F", as_sgroup, "in ");
            femalePlus2 = GetCountPesonal("(member_date between {0} and {1}) and member_status = 1 ", adtm_start, adtm_end, "F", as_sgroup, " in ");
            femaleRes2 = GetCountPesonal("(resign_date between {0} and {1}) ", adtm_start, adtm_end, "F", as_sgroup, " in ");

            sh_amt2 = GetShareValues(adtm_start, adtm_end, as_sgroup, " in ");
            sh_swd2 = GetSWDShareValues(adtm_start, adtm_end, as_sgroup, " in ");

            decimal sh_amt2_1 = GetShareValues2(adtm_start, adtm_end, as_sgroup, " in ");
            decimal sh_swd2_1 = GetSWDShareValues2(adtm_start, adtm_end, as_sgroup, " in ");
          
           


            bfmale2 = (malemaster2 - malePlus2) + maleRes2;
            bffemale2 = (femalemaster2 - femalePlus2) + femaleRes2;
                bfsh_amt = (sh_master1-  sh_amt)+sh_swd;
                bfsh_amt2 = (sh_master2 - sh_amt2) + sh_swd2;
         //   bfsh_amt2 = GetBFShareValues(adtm_start, adtm_end, as_sgroup, " in ");
            WebUtil.QuerySdt("delete from  report_sharePaper");
            WebUtil.QuerySdt(@" insert into report_sharePaper(coop_id , membtype, bfmale , malePlus  , maleRes,
 bffemale , femalePlus , femaleRes , bfsh_amt , sh_amt , sh_swd,seq_no)  values('" + state.SsCoopControl + "','" +"สมาชิกสมทบ ("+ dsMain.DATA[0].membgroup_start+")" + "' ,"+
                                        " " + bfmale2 + "," + malePlus2 + "," + maleRes2 + "," + bffemale2 + "," + femalePlus2 + ", " + femaleRes2 + "," + bfsh_amt2 + "," + sh_amt2_1 + "," + sh_swd2_1 + ",2 ) ");

            WebUtil.QuerySdt(@" insert into report_sharePaper(coop_id , membtype, bfmale , malePlus  , maleRes,
 bffemale , femalePlus , femaleRes , bfsh_amt , sh_amt , sh_swd,seq_no)  values('" + state.SsCoopControl + "','สมาชิก' ," +
                                        " " + bfmale + "," + malePlus + "," + maleRes + "," + bffemale + "," + femalePlus + ", " + femaleRes + "," + bfsh_amt + "," + sh_amt_2 + "," + sh_swd_2 + ",1 ) ");

            
                iReportArgument arg = new iReportArgument();

                arg.Add("coop_id", iReportArgumentType.String, coop_id);
                arg.Add("adtm_sdate", iReportArgumentType.Date, adtm_start);
                arg.Add("adtm_edate", iReportArgumentType.Date, adtm_end);
                //arg.Add("membgroup_start", iReportArgumentType.String, as_sgroup);
                //arg.Add("membgroup_end", iReportArgumentType.String, as_egroup);

                iReportBuider report = new iReportBuider(this, arg);
                report.Retrieve();
            }
            catch (Exception ex)
            {
                LtServerMessage.Text = WebUtil.ErrorMessage(ex);
            }
        }

        private decimal GetSWDShareValues(DateTime adtm_start, DateTime adtm_end, string as_sgroup,   string as_check)
        {
            decimal bfmale = 0;
            String sqlbfmale = @"select
    abs(sum( case when ( shsharestatement.shritemtype_code in ('SWD','RWD','STL','RTL')  and shsharestatement.item_status=1  and TRUNC(shsharestatement.slip_date) >= {0}
  )  then shsharestatement.share_amount  *shsharetype.share_value * shucfshritemtype.sign_flag else 0 end )) as share_swd
from shsharestatement , shsharetype,shucfshritemtype,MBMEMBMASTER where
( shsharestatement.member_no (+) = mbmembmaster.member_no) and
 ( SHSHARESTATEMENT.SHRITEMTYPE_CODE = SHUCFSHRITEMTYPE.SHRITEMTYPE_CODE ) and
         ( SHSHARESTATEMENT.SHARETYPE_CODE = SHSHARETYPE.SHARETYPE_CODE ) and
  trim(MBMEMBMASTER.membgroup_code) " + as_check + " (" + as_sgroup + ")  and shsharestatement.coop_id = '" + state.SsCoopControl + "'";
            sqlbfmale = WebUtil.SQLFormat(sqlbfmale, adtm_start, adtm_end);
            Sdt bffmale = WebUtil.QuerySdt(sqlbfmale);
            if (bffmale.Next())
            {
                bfmale = bffmale.GetDecimal("share_swd");
            }
            bffmale.Clear();
            return bfmale;
        }
        private decimal GetSWDShareValues2(DateTime adtm_start, DateTime adtm_end, string as_sgroup, string as_check)
        {
            decimal bfmale = 0;
            String sqlbfmale = @"select
    abs(sum( case when ( shsharestatement.shritemtype_code in ('SWD','RWD','STL','RTL')  and shsharestatement.item_status=1  and TRUNC(shsharestatement.slip_date) between {0} and  {1}
  )  then shsharestatement.share_amount  *shsharetype.share_value * shucfshritemtype.sign_flag else 0 end )) as share_swd
from shsharestatement , shsharetype,shucfshritemtype,MBMEMBMASTER where
( shsharestatement.member_no (+) = mbmembmaster.member_no) and
 ( SHSHARESTATEMENT.SHRITEMTYPE_CODE = SHUCFSHRITEMTYPE.SHRITEMTYPE_CODE ) and
         ( SHSHARESTATEMENT.SHARETYPE_CODE = SHSHARETYPE.SHARETYPE_CODE ) and
  trim(MBMEMBMASTER.membgroup_code) " + as_check + " (" + as_sgroup + ")  and shsharestatement.coop_id = '" + state.SsCoopControl + "'";
            sqlbfmale = WebUtil.SQLFormat(sqlbfmale, adtm_start, adtm_end);
            Sdt bffmale = WebUtil.QuerySdt(sqlbfmale);
            if (bffmale.Next())
            {
                bfmale = bffmale.GetDecimal("share_swd");
            }
            bffmale.Clear();
            return bfmale;
        }

        private decimal GetShareValues(DateTime adtm_start, DateTime adtm_end, string as_sgroup, string as_check)
        {
            decimal bfmale = 0;
            String sqlbfmale = @"select
     sum( case when ( shsharestatement.shritemtype_code in ('SPX' ,'SPN', 'SPM' )  and shsharestatement.item_status=1 
and TRUNC(shsharestatement.slip_date) >= {0})
then shsharestatement.share_amount *shsharetype.share_value * shucfshritemtype.sign_flag  else 0 end ) as share_px
from shsharestatement , shsharetype,shucfshritemtype,MBMEMBMASTER where 
( shsharestatement.member_no (+) = mbmembmaster.member_no) and
 ( SHSHARESTATEMENT.SHRITEMTYPE_CODE = SHUCFSHRITEMTYPE.SHRITEMTYPE_CODE ) and
         ( SHSHARESTATEMENT.SHARETYPE_CODE = SHSHARETYPE.SHARETYPE_CODE ) and
  trim(MBMEMBMASTER.membgroup_code) " + as_check + " (" + as_sgroup + ")  and shsharestatement.coop_id = '" + state.SsCoopControl + "'";
            sqlbfmale = WebUtil.SQLFormat(sqlbfmale, adtm_start, adtm_end);
            Sdt bffmale = WebUtil.QuerySdt(sqlbfmale);
            if (bffmale.Next())
            {
                bfmale = bffmale.GetDecimal("share_px");
            }
            bffmale.Clear();
            return bfmale;
        }
        private decimal GetShareValues2(DateTime adtm_start, DateTime adtm_end, string as_sgroup, string as_check)
        {
            decimal bfmale = 0;
            String sqlbfmale = @"select
     sum( case when ( shsharestatement.shritemtype_code in ('SPX' ,'SPN', 'SPM' )  and shsharestatement.item_status=1 
and TRUNC(shsharestatement.slip_date) between {0} and  {1})
then shsharestatement.share_amount *shsharetype.share_value * shucfshritemtype.sign_flag  else 0 end ) as share_px
from shsharestatement , shsharetype,shucfshritemtype,MBMEMBMASTER where 
( shsharestatement.member_no (+) = mbmembmaster.member_no) and
 ( SHSHARESTATEMENT.SHRITEMTYPE_CODE = SHUCFSHRITEMTYPE.SHRITEMTYPE_CODE ) and
         ( SHSHARESTATEMENT.SHARETYPE_CODE = SHSHARETYPE.SHARETYPE_CODE ) and
  trim(MBMEMBMASTER.membgroup_code) " + as_check + " (" + as_sgroup + ")  and shsharestatement.coop_id = '" + state.SsCoopControl + "'";
            sqlbfmale = WebUtil.SQLFormat(sqlbfmale, adtm_start, adtm_end);
            Sdt bffmale = WebUtil.QuerySdt(sqlbfmale);
            if (bffmale.Next())
            {
                bfmale = bffmale.GetDecimal("share_px");
            }
            bffmale.Clear();
            return bfmale;
        }

        private decimal GetBFShareValues(DateTime adtm_start, DateTime adtm_end, string as_sgroup, string as_check)
        {
            decimal bfmale = 0;
            String sqlbfmale = @"select sum(sh.sharestk_amt)*10 as share_px from shsharemaster inner join shsharestatement sh on shsharemaster.member_no = sh.member_no left join mbmembmaster mb on sh.member_no = mb.member_no
where shritemtype_code = 'B/F' and (shsharemaster.payment_status=1 or mb.resign_date is null or mb.resign_date <= {1} or mb.member_date <={1}) and
 slip_date = (select accstart_date  from cmaccountyear where accstart_date <= {0} and accend_date >=  {1}) and
  trim(mb.membgroup_code) " + as_check + " (" + as_sgroup + ")  and mb.coop_id = '" + state.SsCoopControl + "'";
            sqlbfmale = WebUtil.SQLFormat(sqlbfmale, adtm_start, adtm_end);
            Sdt bffmale = WebUtil.QuerySdt(sqlbfmale);
            if (bffmale.Next())
            {
                bfmale = bffmale.GetDecimal("share_px");
            }
            bffmale.Clear();
            return bfmale;
        }

        private int GetCountPesonal(string p, DateTime adtm_start, DateTime adtm_end, string p_2, string as_sgroup, string as_check)
        {
            int bfmale = 0;
            String sqlbfmale = "select count(mbmembmaster.member_no) as member_no  from mbmembmaster inner join shsharemaster on mbmembmaster.member_no = shsharemaster.member_no  where " + p + " and    mbmembmaster.sex = '" + p_2 + "' and trim(mbmembmaster.membgroup_code) " + as_check + " (" + as_sgroup + ")  and mbmembmaster.coop_id = '" + state.SsCoopControl + "'";
            sqlbfmale = WebUtil.SQLFormat(sqlbfmale, adtm_start, adtm_end);
            Sdt bffmale = WebUtil.QuerySdt(sqlbfmale);
            if (bffmale.Next())
            {
                bfmale = bffmale.GetInt32("member_no");
            }
            bffmale.Clear();
            return bfmale;
            //and membtype_code =80
        }

        public void WebSheetLoadEnd()
        {

        }
    }
}