public IHttpActionResult GetPK13_1byMonth(int month, int year)
        {
            PK13Model result = new PK13Model();

            result.DetailData = new List <PK13DetailModel>();
            DateTime BDate = new DateTime(year, month, 1);
            DateTime EDate = BDate.AddMonths(1).AddDays(-1);

            result = getPK13Data(BDate, EDate);
            if (!string.IsNullOrEmpty(result.ErrorMessage))
            {
                return(BadRequest(result.ErrorMessage));
            }
            return(Json(result));
        }
        public IHttpActionResult GetPK13_1ByDate(int S_Day, int S_Month, int S_Year, int E_Day, int E_Month, int E_Year)
        {
            PK13Model result = new PK13Model();

            result.DetailData = new List <PK13DetailModel>();
            DateTime BDate = new DateTime(S_Year, S_Month, S_Day);
            DateTime EDate = new DateTime(E_Year, E_Month, E_Day);

            result = getPK13Data(BDate, EDate);
            if (!string.IsNullOrEmpty(result.ErrorMessage))
            {
                return(BadRequest(result.ErrorMessage));
            }
            return(Json(result));
        }
        public PK13Model getPK13Data(DateTime BDate, DateTime EDate)
        {
            DBConnector.DBConnector conn = new DBConnector.DBConnector();
            UtilLibs  Ulib   = new UtilLibs();
            PK13Model result = new PK13Model();

            result.DetailData = new List <PK13DetailModel>();
            MasterData masterData = GetMasterData();

            result.PartyName    = masterData.PartyName;
            result.AnnouDay     = DateTime.Now.Day.ToString();
            result.AnnouMonth   = Ulib.getMonthShortName("th", DateTime.Now.Month);
            result.AnnouYear    = (DateTime.Now.Year + 543).ToString();
            result.SDay         = BDate.Day.ToString();
            result.SMonth       = Ulib.getMonthShortName("th", BDate.Month);
            result.SYear        = (BDate.Year + 543).ToString();
            result.EDay         = EDate.Day.ToString();
            result.EMonth       = Ulib.getMonthShortName("th", EDate.Month);
            result.EYear        = (EDate.Year + 543).ToString();
            result.ErrorMessage = "";
            double SumCash  = 0;
            double SumAsset = 0;
            string SQLString;

            if (conn.OpenConnection())
            {
                try
                {
                    SQLString = @"select tb1.DocumentRunno, tb1.DonatorRunno, tb1.DocumentDate,
                                  tb2.Amount, tb2.DonateTypeRunno, tb2.DetailRunno, 
                                  tb3.DonatorPreName, tb3.DonatorName, tb3.DonatorSurName, tb3.DonatorCitizenId,
                                  tb3.HouseNumber, tb3.Moo, tb3.Building, tb3.Soi, tb3.Road, tb3.Tambon, 
                                  tb3.Amphur, tb3.Province, tb3.Zipcode, tb3.Telephone,
                                  tb3.Career, tb3.Nationality
                                  from donatedata tb1, donatedetaildata tb2, donatordata tb3
                                  where tb1.DocumentRunno = tb2.DocumentRunno
                                  and tb1.DonatorRunno = tb3.DonatorRunno
                                  and tb1.DocumentDate >= @BDATE
                                  and tb1.DocumentDate <= @EDATE
                                  and (tb3.DonatorTaxId is null or tb3.DonatorTaxId = '')
                                  order by DocumentDate, DonatorName";
                    MySqlCommand qExe = new MySqlCommand
                    {
                        Connection  = conn.connection,
                        CommandText = SQLString
                    };
                    qExe.Parameters.AddWithValue("BDATE", BDate);
                    qExe.Parameters.AddWithValue("EDATE", EDate);
                    MySqlDataReader dataReader = qExe.ExecuteReader();
                    if (dataReader.HasRows)
                    {
                        DateTime        vDate  = new DateTime();
                        PK13DetailModel detail = new PK13DetailModel();

                        while (dataReader.Read())
                        {
                            if (vDate != DateTime.Parse(dataReader["DocumentDate"].ToString()) ||
                                detail.Name != dataReader["DonatorName"].ToString())
                            {
                                if (vDate != new DateTime())
                                {
                                    result.DetailData.Add(detail);
                                }

                                vDate            = DateTime.Parse(dataReader["DocumentDate"].ToString());
                                detail           = new PK13DetailModel();
                                detail.PreName   = dataReader["DonatorPreName"].ToString();
                                detail.Name      = dataReader["DonatorName"].ToString();
                                detail.SurName   = dataReader["DonatorSurName"].ToString();
                                detail.Telephone = dataReader["Telephone"].ToString();
                                detail.Addr1     = GetAddrRow1(dataReader["HouseNumber"].ToString(),
                                                               dataReader["Moo"].ToString(),
                                                               dataReader["Building"].ToString(),
                                                               dataReader["Soi"].ToString(),
                                                               dataReader["Road"].ToString());
                                detail.Addr2 = GetAddrRow2(dataReader["Tambon"].ToString(),
                                                           dataReader["Amphur"].ToString(),
                                                           dataReader["Province"].ToString(),
                                                           dataReader["Zipcode"].ToString());
                                detail.CitizenID = dataReader["DonatorCitizenId"].ToString();
                                detail.DateStr   = vDate.Day + " " + Ulib.getMonthShortName("th", vDate.Month) + " " +
                                                   (vDate.Year + 543);
                                detail.Career      = dataReader["Career"].ToString();
                                detail.Nationality = dataReader["Nationality"].ToString();
                                detail.Cash        = 0;
                                detail.Asset       = 0;
                            }
                            else
                            {
                                if (dataReader["DonateTypeRunno"].ToString() == "1" ||
                                    dataReader["DonateTypeRunno"].ToString() == "5" ||
                                    dataReader["DonateTypeRunno"].ToString() == "6")
                                {
                                    detail.Cash = detail.Cash + double.Parse(dataReader["Amount"].ToString());
                                    SumCash     = SumCash + double.Parse(dataReader["Amount"].ToString());
                                }
                                else
                                {
                                    detail.Asset = detail.Asset + double.Parse(dataReader["Amount"].ToString());
                                    SumAsset     = SumAsset + double.Parse(dataReader["Amount"].ToString());
                                }
                            }
                        }
                        result.DetailData.Add(detail);
                        result.TotalCash      = SumCash;
                        result.TotalAsset     = SumAsset;
                        result.TotalAmount    = SumCash + SumAsset;
                        result.TotalAmountStr = Ulib.ThaiBaht(result.TotalAmount.ToString());
                        return(result);
                    }
                    else
                    {
                        result.ErrorMessage = "Data Empty";
                        return(result);
                    }
                }
                catch (Exception e)
                {
                    result.ErrorMessage = e.Message;
                    return(result);
                }
            }
            else
            {
                result.ErrorMessage = "Database connect fail!";
                return(result);
            }
        }