public IHttpActionResult GetPK13Term() { List <TermofPK13> result = new List <TermofPK13>(); UtilLibs Ulib = new UtilLibs(); DBConnector.DBConnector conn = new DBConnector.DBConnector(); string SQLString = @"select distinct year(DocumentDate) vYear, month(DocumentDate) vMonth from donatedata"; if (conn.OpenConnection()) { try { MySqlCommand qExe = new MySqlCommand { Connection = conn.connection, CommandText = SQLString }; MySqlDataReader dataReader = qExe.ExecuteReader(); if (dataReader.HasRows) { while (dataReader.Read()) { result.Add(new TermofPK13 { Month = dataReader["vMonth"].ToString(), Year = dataReader["vYear"].ToString(), TermName = Ulib.getMonthShortName("th", int.Parse(dataReader["vMonth"].ToString())) + " " + (int.Parse(dataReader["vYear"].ToString()) + 543).ToString() }); } return(Json(result)); } else { return(BadRequest("Data Empty")); } } catch (Exception e) { return(BadRequest(e.Message)); } } else { return(BadRequest("Database connect fail!")); } }
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); } }