public IHttpActionResult PK9GetByID(string id)
{
UtilLibs utilLibs = new UtilLibs();
ReceiptDataModel result = new ReceiptDataModel();
DBConnector.DBConnector conn = new DBConnector.DBConnector();
if (conn.OpenConnection())
{
try
{
string sqlString;
if (!string.IsNullOrEmpty(id))
{
sqlString = @"select tb1.*,
tb2.MemberId PayerID, tb2.MemberPreName PreName,
tb2.MemberName Name, tb2.MemberSurname SurName,
tb2.HouseNumber HouseNum, tb2.Soi, tb2.Road,
tb2.Moo, tb2.Building, tb2.Tambon,
tb2.Amphur, tb2.Province, tb2.Zipcode,
tb2.Telephone, tb2.MemberCitizenId CitizenID
from receiptdata tb1, memberdata tb2
where tb1.PayerRunno = tb2.MemberRunno
and tb1.PayerType = '1'
and tb1.DocumentRunno = @DocumentRunno
union
select tb1.*,
tb2.DonatorId PayerID, tb2.DonatorPreName PreName,
tb2.DonatorName Name, tb2.DonatorSurName SurName,
tb2.HouseNumber, tb2.Soi, tb2.Road,
tb2.Moo, tb2.Building, tb2.Tambon,
tb2.Amphur, tb2.Province, tb2.Zipcode,
tb2.Telephone, tb2.DonatorCitizenId CitizenID
from receiptdata tb1, donatordata tb2
where tb1.PayerRunno = tb2.DonatorRunno
and tb1.PayerType = '2'
and tb1.DocumentRunno = @DocumentRunno";
}
else
{
return(Json("Document Number is blank!"));
}
MySqlCommand qExe = new MySqlCommand
{
Connection = conn.connection,
CommandText = sqlString
};
qExe.Parameters.AddWithValue("@DocumentRunno", id);
MySqlDataReader dataReader = qExe.ExecuteReader();
while (dataReader.Read())
{
result.DocumentRunno = int.Parse(dataReader["documentrunno"].ToString());
result.DocumentBookNumber = dataReader["documentbooknumber"].ToString();
result.DocumentNumber = dataReader["documentnumber"].ToString();
result.DocumentDate = Convert.ToDateTime(dataReader["documentdate"].ToString(), new CultureInfo("en-US"));
result.PayerType = dataReader["PayerType"].ToString();
result.PayerRunno = int.Parse(dataReader["PayerRunno"].ToString());
result.AsReceiptTo = dataReader["AsReceiptTo"].ToString();
result.AsReceiptToRemark = dataReader["AsReceiptToRemark"].ToString();
result.ReceiptAmount = double.Parse(dataReader["ReceiptAmount"].ToString());
result.HouseNumber = dataReader["HouseNum"].ToString();
result.Soi = dataReader["Soi"].ToString();
result.Road = dataReader["Road"].ToString();
result.Moo = dataReader["Moo"].ToString();
result.Building = dataReader["Building"].ToString();
result.Tambon = dataReader["Tambon"].ToString();
result.Amphur = dataReader["Amphur"].ToString();
result.Province = dataReader["Province"].ToString();
result.Zipcode = dataReader["Zipcode"].ToString();
result.Telephone = dataReader["Telephone"].ToString();
result.PayerId = dataReader["PayerID"].ToString();
result.PayerName = dataReader["PreName"].ToString() + dataReader["Name"].ToString() + " " + dataReader["SurName"].ToString();
result.ReceiptPayType = dataReader["ReceiptPayType"].ToString();
result.ReceiptDate = Convert.ToDateTime(dataReader["ReceiptDate"].ToString(), new CultureInfo("en-US"));
result.ReceiptBank = dataReader["ReceiptBank"].ToString();
result.BankName = utilLibs.GetBankName(dataReader["ReceiptBank"].ToString());
result.ReceiptBillNumber = dataReader["ReceiptBillNumber"].ToString();
result.ReceiptChqBank = dataReader["ReceiptChqBank"].ToString();
result.ReceiptChqNumber = dataReader["ReceiptChqNumber"].ToString();
result.ReceiptOther = dataReader["ReceiptOther"].ToString();
result.CitizenId = dataReader["CitizenID"].ToString();
result.AmountStr = utilLibs.ThaiBaht(dataReader["ReceiptAmount"].ToString());
}
dataReader.Close();
conn.CloseConnection();
return(Json(result));
}
catch (Exception e)
{
return(BadRequest(e.Message));
}
}
else
{
return(BadRequest("Database connect fail!"));
}
}
public IHttpActionResult PK9ListAll()
{
UtilLibs utilLibs = new UtilLibs();
List <ReceiptDataModel> result = new List <ReceiptDataModel>();
DBConnector.DBConnector conn = new DBConnector.DBConnector();
if (conn.OpenConnection())
{
try
{
string sqlString = @"select tb1.*,
tb2.MemberId PayerID, tb2.MemberPreName PreName,
tb2.MemberName Name, tb2.MemberSurname SurName,
tb2.HouseNumber HouseNum, tb2.Soi, tb2.Road,
tb2.Moo, tb2.Building, tb2.Tambon,
tb2.Amphur, tb2.Province, tb2.Zipcode,
tb2.Telephone, tb2.MemberCitizenId CitizenID
from receiptdata tb1, memberdata tb2
where tb1.PayerRunno = tb2.MemberRunno
and tb1.PayerType = '1'
union
select tb1.*,
tb2.DonatorId PayerID, tb2.DonatorPreName PreName,
tb2.DonatorName Name, tb2.DonatorSurName SurName,
tb2.HouseNumber, tb2.Soi, tb2.Road,
tb2.Moo, tb2.Building, tb2.Tambon,
tb2.Amphur, tb2.Province, tb2.Zipcode,
tb2.Telephone, tb2.DonatorCitizenId CitizenID
from receiptdata tb1, donatordata tb2
where tb1.PayerRunno = tb2.DonatorRunno
and tb1.PayerType = '2'
order by DocumentRunno";
/*
* string sqlString = @"select tb1.*, tb2.* from receiptdata tb1, memberdata tb2
* where tb1.PayerRunno = tb2.MemberRunno
* order by tb1.DocumentRunno";
*/
MySqlCommand qExe = new MySqlCommand
{
Connection = conn.connection,
CommandText = sqlString
};
MySqlDataReader dataReader = qExe.ExecuteReader();
while (dataReader.Read())
{
ReceiptDataModel detail = new ReceiptDataModel
{
DocumentRunno = int.Parse(dataReader["documentrunno"].ToString()),
DocumentBookNumber = dataReader["documentbooknumber"].ToString(),
DocumentNumber = dataReader["documentnumber"].ToString(),
DocumentDate = Convert.ToDateTime(dataReader["DocumentDate"].ToString(), new CultureInfo("en-US")),
PayerType = dataReader["PayerType"].ToString(),
PayerRunno = int.Parse(dataReader["PayerRunno"].ToString()),
AsReceiptTo = dataReader["AsReceiptTo"].ToString(),
AsReceiptToRemark = dataReader["AsReceiptToRemark"].ToString(),
ReceiptAmount = double.Parse(dataReader["ReceiptAmount"].ToString()),
HouseNumber = dataReader["HouseNum"].ToString(),
Soi = dataReader["Soi"].ToString(),
Road = dataReader["Road"].ToString(),
Moo = dataReader["Moo"].ToString(),
Building = dataReader["Building"].ToString(),
Tambon = dataReader["Tambon"].ToString(),
Amphur = dataReader["Amphur"].ToString(),
Province = dataReader["Province"].ToString(),
Zipcode = dataReader["Zipcode"].ToString(),
Telephone = dataReader["Telephone"].ToString(),
PayerId = dataReader["PayerID"].ToString(),
PayerName = dataReader["PreName"].ToString() + dataReader["Name"].ToString() + " " + dataReader["SurName"].ToString(),
ReceiptPayType = dataReader["ReceiptPayType"].ToString(),
ReceiptDate = Convert.ToDateTime(dataReader["ReceiptDate"].ToString(), new CultureInfo("en-US")),
ReceiptBank = dataReader["ReceiptBank"].ToString(),
BankName = utilLibs.GetBankName(dataReader["ReceiptBank"].ToString()),
ReceiptBillNumber = dataReader["ReceiptBillNumber"].ToString(),
ReceiptChqBank = dataReader["ReceiptChqBank"].ToString(),
ReceiptChqNumber = dataReader["ReceiptChqNumber"].ToString(),
ReceiptOther = dataReader["ReceiptOther"].ToString(),
CitizenId = dataReader["CitizenID"].ToString(),
AmountStr = utilLibs.ThaiBaht(dataReader["ReceiptAmount"].ToString())
};
result.Add(detail);
}
dataReader.Close();
conn.CloseConnection();
return(Json(result));
}
catch (Exception e)
{
return(BadRequest(e.Message));
}
}
else
{
return(BadRequest("Database connect fail!"));
}
}