public IHttpActionResult GetPreNameListbyRunno(string id)
{
PrenameModel result = new PrenameModel();
DBConnector.DBConnector conn = new DBConnector.DBConnector();
string SQLString;
if (conn.OpenConnection())
{
MySqlCommand qExe = new MySqlCommand();
qExe.Connection = conn.connection;
SQLString = @"select PreNameRunno, PreName from prename where PreNameRunno = @PreNameRunno";
qExe.CommandText = SQLString;
qExe.Parameters.AddWithValue("@PreNameRunno", id);
MySqlDataReader dataReader = qExe.ExecuteReader();
while (dataReader.Read())
{
PrenameModel detail = new PrenameModel();
detail.PreNameRunno = int.Parse(dataReader["PreNameRunno"].ToString());
detail.PreName = dataReader["PreName"].ToString();
result = detail;
}
return(Json(result));
}
else
{
return(BadRequest("Database connect fail!"));
}
}
public IHttpActionResult getAmphurData(string province)
{
List <zipcodedata> result = new List <zipcodedata>();
DBConnector.DBConnector conn = new DBConnector.DBConnector();
string SQLString;
if (conn.OpenConnection())
{
MySqlCommand qExe = new MySqlCommand();
qExe.Connection = conn.connection;
SQLString = @"select distinct province, amphur from zip_data where province = '" + province + @"' order by amphur";
qExe.CommandText = SQLString;
MySqlDataReader dataReader = qExe.ExecuteReader();
while (dataReader.Read())
{
zipcodedata detail = new zipcodedata();
detail.province = dataReader["province"].ToString();
detail.amphur = dataReader["amphur"].ToString();
detail.tambon = "";
detail.zipcode = "";
result.Add(detail);
}
return(Json(result));
}
else
{
return(BadRequest("Database connect fail!"));
}
}
public IHttpActionResult AddDonateTypeData([FromBody] BankData item)
{
DBConnector.DBConnector conn = new DBConnector.DBConnector();
string SQLString;
if (conn.OpenConnection())
{
SQLString = @"insert into bank_name (bank_code, bank_name) values (@bank_code, @bank_name)";
MySqlCommand qExe = new MySqlCommand
{
Connection = conn.connection,
CommandText = SQLString
};
qExe.Parameters.AddWithValue("@bank_code", item.BankCode);
qExe.Parameters.AddWithValue("@bank_name", item.BankName);
qExe.ExecuteNonQuery();
conn.CloseConnection();
return(Json(new ResultDataModel {
success = true, errorMessage = "", returnRunno = item.BankCode
}));
}
else
{
return(Json(new ResultDataModel {
success = false, errorMessage = "Database connect fail!", returnRunno = ""
}));
}
}
public IHttpActionResult GetPartyPositionList()
{
List <partyposition> result = new List <partyposition>();
DBConnector.DBConnector conn = new DBConnector.DBConnector();
string SQLString;
if (conn.OpenConnection())
{
MySqlCommand qExe = new MySqlCommand();
qExe.Connection = conn.connection;
SQLString = @"select positionrunno, positionno, positionname from partyposition
order by positionno";
qExe.CommandText = SQLString;
MySqlDataReader dataReader = qExe.ExecuteReader();
while (dataReader.Read())
{
partyposition detail = new partyposition();
detail.PositionRunno = int.Parse(dataReader["PositionRunno"].ToString());
detail.PositionNo = int.Parse(dataReader["PositionNo"].ToString());
detail.PositionName = dataReader["PositionName"].ToString();
result.Add(detail);
}
return(Json(result));
}
else
{
return(BadRequest("Database connect fail!"));
}
}
public IHttpActionResult AddDonateTypeData([FromBody] DonateTypeData item)
{
DBConnector.DBConnector conn = new DBConnector.DBConnector();
string SQLString;
if (conn.OpenConnection())
{
SQLString = @"insert into donatetype (DonateTypeName) values (@DonateTypeName)";
MySqlCommand qExe = new MySqlCommand
{
Connection = conn.connection,
CommandText = SQLString
};
qExe.Parameters.AddWithValue("@DonateTypeName", item.DonateTypeName);
qExe.ExecuteNonQuery();
long returnid = qExe.LastInsertedId;
conn.CloseConnection();
return(Json(new ResultDataModel {
success = true, errorMessage = "", returnRunno = returnid.ToString()
}));
}
else
{
return(Json(new ResultDataModel {
success = false, errorMessage = "Database connect fail!", returnRunno = ""
}));
}
}
public IHttpActionResult EditDonateTypeData([FromBody] DonateTypeData item)
{
DBConnector.DBConnector conn = new DBConnector.DBConnector();
string SQLString;
if (conn.OpenConnection())
{
SQLString = @"UPDATE donatetype SET DonateTypeRunno = @DonateTypeRunno, DonateTypeName = @DonateTypeName WHERE DonateTypeRunno = @DonateTypeRunno";
MySqlCommand qExe = new MySqlCommand
{
Connection = conn.connection,
CommandText = SQLString
};
qExe.Parameters.AddWithValue("@DonateTypeRunno", item.DonateTypeRunno);
qExe.Parameters.AddWithValue("@DonateTypeName", item.DonateTypeName);
qExe.ExecuteNonQuery();
conn.CloseConnection();
return(Json(new ResultDataModel {
success = true, errorMessage = "", returnRunno = ""
}));
}
else
{
return(Json(new ResultDataModel {
success = false, errorMessage = "Database connect fail!", returnRunno = ""
}));
}
}
public IHttpActionResult GetPreNameList()
{
List <PrenameModel> result = new List <PrenameModel>();
DBConnector.DBConnector conn = new DBConnector.DBConnector();
string SQLString;
if (conn.OpenConnection())
{
MySqlCommand qExe = new MySqlCommand();
qExe.Connection = conn.connection;
SQLString = @"select PreNameRunno, PreName from prename order by PreNameRunno";
qExe.CommandText = SQLString;
MySqlDataReader dataReader = qExe.ExecuteReader();
while (dataReader.Read())
{
PrenameModel detail = new PrenameModel();
detail.PreNameRunno = int.Parse(dataReader["PreNameRunno"].ToString());
detail.PreName = dataReader["PreName"].ToString();
result.Add(detail);
}
return(Json(result));
}
else
{
return(BadRequest("Database connect fail!"));
}
}
public IHttpActionResult PK9DataAdd([FromBody] ReceiptDataModel item)
{
DBConnector.DBConnector conn = new DBConnector.DBConnector();
UtilLibs utilLibs = new UtilLibs();
DocumentNumber docNum = new DocumentNumber();
if (conn.OpenConnection())
{
try
{
string SQLString = @"INSERT INTO receiptdata(DocumentBookNumber, DocumentNumber, DocumentDate, PayerType,
PayerRunno, AsReceiptTo, AsReceiptToRemark,
ReceiptAmount, ReceiptPaytype, ReceiptDate, ReceiptBank, ReceiptBillNumber,
ReceiptChqBank, ReceiptChqNumber, ReceiptOther)
VALUES (@DocumentBookNumber, @DocumentNumber, @DocumentDate, @PayerType, @PayerRunno,
@AsReceiptTo, @AsReceiptToRemark, @ReceiptAmount,
@ReceiptPaytype, @ReceiptDate, @ReceiptBank, @ReceiptBillNumber, @ReceiptChqBank,
@ReceiptChqNumber, @ReceiptOther) ";
MySqlCommand qExe = new MySqlCommand
{
Connection = conn.connection,
CommandText = SQLString
};
docNum = utilLibs.GetDocumentNo("pk9");
qExe.Parameters.AddWithValue("@documentbooknumber", docNum.BookNo);
qExe.Parameters.AddWithValue("@documentnumber", docNum.DocumentNo);
qExe.Parameters.AddWithValue("@documentdate", Convert.ToDateTime(item.DocumentDate, new CultureInfo("en-US")));
qExe.Parameters.AddWithValue("@PayerType", item.PayerType);
qExe.Parameters.AddWithValue("@PayerRunno", item.PayerRunno);
qExe.Parameters.AddWithValue("@AsReceiptTo", item.AsReceiptTo);
qExe.Parameters.AddWithValue("@AsReceiptToRemark", item.AsReceiptToRemark);
qExe.Parameters.AddWithValue("@ReceiptAmount", item.ReceiptAmount);
qExe.Parameters.AddWithValue("@ReceiptPaytype", item.ReceiptPayType);
qExe.Parameters.AddWithValue("@ReceiptDate", Convert.ToDateTime(item.ReceiptDate, new CultureInfo("en-US")));
qExe.Parameters.AddWithValue("@ReceiptBank", item.ReceiptBank);
qExe.Parameters.AddWithValue("@ReceiptBillNumber", item.ReceiptBillNumber);
qExe.Parameters.AddWithValue("@ReceiptChqBank", item.ReceiptChqBank);
qExe.Parameters.AddWithValue("@ReceiptChqNumber", item.ReceiptChqNumber);
qExe.Parameters.AddWithValue("@ReceiptOther", item.ReceiptOther);
qExe.ExecuteNonQuery();
long returnid = qExe.LastInsertedId;
conn.CloseConnection();
return(Json(new ResultDataModel {
success = true, errorMessage = "", returnRunno = returnid.ToString()
}));
}
catch (Exception e)
{
return(Json(new ResultDataModel {
success = false, errorMessage = e.Message, returnRunno = ""
}));
}
}
else
{
return(Json(new ResultDataModel {
success = false, errorMessage = "Database connect fail!", returnRunno = ""
}));
}
}
public IHttpActionResult DeleteDonateTypeData(string id)
{
DBConnector.DBConnector conn = new DBConnector.DBConnector();
string SQLString;
if (conn.OpenConnection())
{
SQLString = @"DELETE FROM donatetype WHERE DonateTypeRunno = @DonateTypeRunno";
MySqlCommand qExe = new MySqlCommand
{
Connection = conn.connection,
CommandText = SQLString
};
if (string.IsNullOrEmpty(id))
{
return(Json(new ResultDataModel {
success = false, errorMessage = "Key is null!", returnRunno = ""
}));
}
qExe.Parameters.AddWithValue("@DonateTypeRunno", id);
qExe.ExecuteNonQuery();
conn.CloseConnection();
return(Json(new ResultDataModel {
success = true, errorMessage = "", returnRunno = ""
}));
}
else
{
return(Json(new ResultDataModel {
success = false, errorMessage = "Database connect fail!", returnRunno = ""
}));
}
}
public void TestMethod2()
{
DBConnector.DBConnector db = new DBConnector.DBConnector();
bool result = db.OpenConnection();
Assert.IsTrue(result);
}
public IHttpActionResult ReadMasterData()
{
MasterData result = new MasterData();
DBConnector.DBConnector conn = new DBConnector.DBConnector();
string SQLString;
if (conn.OpenConnection())
{
MySqlCommand qExe = new MySqlCommand();
qExe.Connection = conn.connection;
SQLString = @"select * from masterdata";
qExe.CommandText = SQLString;
MySqlDataReader dataReader = qExe.ExecuteReader();
while (dataReader.Read())
{
result.partyname = dataReader["partyname"].ToString();
result.housenumber = dataReader["housenumber"].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();
}
return(Json(result));
}
else
{
return(BadRequest("Database connect fail!"));
}
}
public IHttpActionResult ReceipDataDelete([FromBody] ReceiptDataModel item)
{
DBConnector.DBConnector conn = new DBConnector.DBConnector();
if (conn.OpenConnection())
{
string SQLString = @"delete from receiptdata where documentrunno = @documentrunno";
MySqlCommand qExe = new MySqlCommand
{
Connection = conn.connection,
CommandText = SQLString
};
if (string.IsNullOrEmpty(item.DocumentRunno))
{
return(BadRequest("Document Key is null!"));
}
qExe.Parameters.AddWithValue("@documentrunno", item.DocumentRunno);
qExe.ExecuteNonQuery();
conn.CloseConnection();
return(Ok());
}
else
{
return(BadRequest("Database connect fail!"));
}
}
public IHttpActionResult EditMasterData([FromBody] MasterData item)
{
DBConnector.DBConnector conn = new DBConnector.DBConnector();
string SQLString;
if (conn.OpenConnection())
{
SQLString = @"UPDATE masterdata SET partyname = @partyname, housenumber = @housenumber, soi = @soi,
road = @road, moo = @moo, building = @building, tambon = @tambon, amphur = @amphur,
province = @province, zipcode = @zipcode, telephone = @telephone";
MySqlCommand qExe = new MySqlCommand
{
Connection = conn.connection,
CommandText = SQLString
};
qExe.Parameters.AddWithValue("@partyname", item.partyname);
qExe.Parameters.AddWithValue("@housenumber", item.housenumber);
qExe.Parameters.AddWithValue("@soi", item.soi);
qExe.Parameters.AddWithValue("@road", item.road);
qExe.Parameters.AddWithValue("@moo", item.moo);
qExe.Parameters.AddWithValue("@building", item.building);
qExe.Parameters.AddWithValue("@tambon", item.tambon);
qExe.Parameters.AddWithValue("@amphur", item.amphur);
qExe.Parameters.AddWithValue("@province", item.province);
qExe.Parameters.AddWithValue("@zipcode", item.zipcode);
qExe.Parameters.AddWithValue("@telephone", item.telephone);
qExe.ExecuteNonQuery();
return(Ok());
}
else
{
return(BadRequest("Database connect fail!"));
}
}
public IHttpActionResult getZipCodeData(string zipcode)
{
zipcodedata result = new zipcodedata();
DBConnector.DBConnector conn = new DBConnector.DBConnector();
string SQLString;
if (conn.OpenConnection())
{
if (!string.IsNullOrEmpty(zipcode))
{
MySqlCommand qExe = new MySqlCommand();
qExe.Connection = conn.connection;
SQLString = @"select * from zip_data where zipcode = '" + zipcode + "'";
qExe.CommandText = SQLString;
MySqlDataReader dataReader = qExe.ExecuteReader();
while (dataReader.Read())
{
result.province = dataReader["province"].ToString();
result.amphur = dataReader["amphur"].ToString();
result.tambon = dataReader["tambon"].ToString();
result.zipcode = dataReader["zipcode"].ToString();
}
}
return(Json(result));
}
else
{
return(BadRequest("Database connect fail!"));
}
}
public IHttpActionResult GetMemberData(string id)
{
MemberData result = new MemberData();
DBConnector.DBConnector conn = new DBConnector.DBConnector();
string SQLString;
if (conn.OpenConnection())
{
try
{
SQLString = @"SELECT t1.*, t2.PositionName
FROM memberdata t1 left join partyposition t2 on t1.PositionNo = t2.PositionNo
where MemberRunno = '" + id + @"'
order by MemberId";
MySqlCommand qExe = new MySqlCommand
{
Connection = conn.connection,
CommandText = SQLString
};
MySqlDataReader dataReader = qExe.ExecuteReader();
while (dataReader.Read())
{
result.MemberRunno = int.Parse(dataReader["MemberRunno"].ToString());
result.MemberId = dataReader["MemberId"].ToString();
result.MemberPreName = dataReader["MemberPreName"].ToString();
result.MemberName = dataReader["MemberName"].ToString();
result.MemberSurname = dataReader["MemberSurname"].ToString();
result.PositionNo = int.Parse(dataReader["PositionNo"].ToString());
result.PositionName = dataReader["PositionName"].ToString();
result.HouseNumber = dataReader["HouseNumber"].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.MemberFullName = dataReader["memberPrename"].ToString() + dataReader["membername"].ToString() +
" " + dataReader["MemberSurName"].ToString();
result.MemberCitizenID = dataReader["MemberCitizenID"].ToString();
}
dataReader.Close();
dataReader.Dispose();
return(Json(result));
}
catch (Exception e)
{
return(BadRequest(e.Message));
}
}
else
{
return(BadRequest("Database connect fail!"));
}
}
public IHttpActionResult ReceipDataList()
{
List <ReceiptDataModel> result = new List <ReceiptDataModel>();
DBConnector.DBConnector conn = new DBConnector.DBConnector();
if (conn.OpenConnection())
{
string sqlString = @"select * from receiptdata order by documentrunno";
MySqlCommand qExe = new MySqlCommand
{
Connection = conn.connection,
CommandText = sqlString
};
MySqlDataReader dataReader = qExe.ExecuteReader();
while (dataReader.Read())
{
ReceiptDataModel detail = new ReceiptDataModel
{
DocumentRunno = dataReader["documentrunno"].ToString(),
DocumentBookNumber = dataReader["documentbooknumber"].ToString(),
DocumentNumber = dataReader["documentnumber"].ToString(),
DocumentDate = Convert.ToDateTime(dataReader["documentdate"].ToString(), new CultureInfo("en-US")),
payerName = dataReader["payername"].ToString(),
payerid = dataReader["payerid"].ToString(),
houseNumber = dataReader["housenumber"].ToString(),
soi = dataReader["soi"].ToString(),
road = dataReader["road"].ToString(),
moo = dataReader["moo"].ToString(),
tambon = dataReader["tambon"].ToString(),
amphur = dataReader["amphur"].ToString(),
province = dataReader["province"].ToString(),
zipcode = dataReader["zipcode"].ToString(),
asReceiptTo = dataReader["asreceiptto"].ToString(),
asReceiptToRemark = dataReader["asreceipttoremark"].ToString(),
receiptAmount = double.Parse(dataReader["receiptamount"].ToString()),
receiptPayType = dataReader["receiptpaytype"].ToString(),
receiptDate = Convert.ToDateTime(dataReader["receiptdate"].ToString(), new CultureInfo("en-US")),
receiptBank = dataReader["receiptbank"].ToString(),
receiptBillNumber = dataReader["receiptbillnumber"].ToString(),
receiptChqBank = dataReader["receiptchqbank"].ToString(),
receiptChqNumber = dataReader["receiptchqnumber"].ToString(),
receiptOther = dataReader["receiptother"].ToString(),
building = dataReader["building"].ToString(),
telephone = dataReader["telephone"].ToString()
};
result.Add(detail);
}
dataReader.Close();
conn.CloseConnection();
return(Json(result));
}
else
{
return(BadRequest("Database connect fail!"));
}
}
public IHttpActionResult ListAllDonator()
{
List <DonatorData> result = new List <DonatorData>();
DBConnector.DBConnector conn = new DBConnector.DBConnector();
string SQLString;
if (conn.OpenConnection())
{
try
{
SQLString = @"SELECT * FROM donatordata order by DonatorId";
MySqlCommand qExe = new MySqlCommand
{
Connection = conn.connection,
CommandText = SQLString
};
MySqlDataReader dataReader = qExe.ExecuteReader();
while (dataReader.Read())
{
DonatorData detail = new DonatorData();
detail.DonatorRunno = int.Parse(dataReader["DonatorRunno"].ToString());
detail.DonatorId = dataReader["DonatorId"].ToString();
detail.DonatorPreName = dataReader["DonatorPreName"].ToString();
detail.DonatorName = dataReader["DonatorName"].ToString();
detail.DonatorSurName = dataReader["DonatorSurName"].ToString();
detail.DonatorCitizenId = dataReader["DonatorCitizenId"].ToString();
detail.DonatorRegisterNo = dataReader["DonatorRegisterNo"].ToString();
detail.DonatorTaxId = dataReader["DonatorTaxId"].ToString();
detail.HouseNumber = dataReader["HouseNumber"].ToString();
detail.Soi = dataReader["Soi"].ToString();
detail.Road = dataReader["Road"].ToString();
detail.Moo = dataReader["Moo"].ToString();
detail.Building = dataReader["Building"].ToString();
detail.Tambon = dataReader["Tambon"].ToString();
detail.Amphur = dataReader["Amphur"].ToString();
detail.Province = dataReader["Province"].ToString();
detail.ZipCode = dataReader["ZipCode"].ToString();
detail.Telephone = dataReader["Telephone"].ToString();
detail.DonatorFullName = dataReader["donatorPrename"].ToString() + dataReader["donatorname"].ToString() +
" " + dataReader["donatorSurname"].ToString();
result.Add(detail);
}
dataReader.Close();
dataReader.Dispose();
return(Json(result));
}
catch (Exception e)
{
return(BadRequest(e.Message));
}
}
else
{
return(BadRequest("Database connect fail!"));
}
}
public IHttpActionResult PK9DataEdit([FromBody] ReceiptDataModel item)
{
DBConnector.DBConnector conn = new DBConnector.DBConnector();
if (conn.OpenConnection())
{
try
{
string SQLString = @"UPDATE receiptdata SET DocumentRunno = @DocumentRunno, DocumentBookNumber = @DocumentBookNumber,
DocumentNumber = @DocumentNumber, DocumentDate = @DocumentDate, PayerType = @PayerType,
PayerRunno = @PayerRunno, AsReceiptTo = @AsReceiptTo, AsReceiptToRemark = @AsReceiptToRemark,
ReceiptAmount = @ReceiptAmount, ReceiptPaytype = @ReceiptPaytype, ReceiptDate = @ReceiptDate,
ReceiptBank = @ReceiptBank, ReceiptBillNumber = @ReceiptBillNumber, ReceiptChqBank = @ReceiptChqBank,
ReceiptChqNumber = @ReceiptChqNumber, ReceiptOther = @ReceiptOther WHERE DocumentRunno = @DocumentRunno;";
MySqlCommand qExe = new MySqlCommand
{
Connection = conn.connection,
CommandText = SQLString
};
qExe.Parameters.AddWithValue("@documentrunno", item.DocumentRunno);
qExe.Parameters.AddWithValue("@documentbooknumber", item.DocumentBookNumber);
qExe.Parameters.AddWithValue("@documentnumber", item.DocumentNumber);
qExe.Parameters.AddWithValue("@documentdate", item.DocumentDate);
qExe.Parameters.AddWithValue("@PayerType", item.PayerType);
qExe.Parameters.AddWithValue("@PayerRunno", item.PayerRunno);
qExe.Parameters.AddWithValue("@AsReceiptTo", item.AsReceiptTo);
qExe.Parameters.AddWithValue("@AsReceiptToRemark", item.AsReceiptToRemark);
qExe.Parameters.AddWithValue("@ReceiptAmount", item.ReceiptAmount);
qExe.Parameters.AddWithValue("@ReceiptPaytype", item.ReceiptPayType);
qExe.Parameters.AddWithValue("@ReceiptDate", item.ReceiptDate);
qExe.Parameters.AddWithValue("@ReceiptBank", item.ReceiptBank);
qExe.Parameters.AddWithValue("@ReceiptBillNumber", item.ReceiptBillNumber);
qExe.Parameters.AddWithValue("@ReceiptChqBank", item.ReceiptChqBank);
qExe.Parameters.AddWithValue("@ReceiptChqNumber", item.ReceiptChqNumber);
qExe.Parameters.AddWithValue("@ReceiptOther", item.ReceiptOther);
qExe.ExecuteNonQuery();
conn.CloseConnection();
return(Json(new ResultDataModel {
success = true, errorMessage = "", returnRunno = ""
}));
}
catch (Exception e)
{
return(Json(new ResultDataModel {
success = false, errorMessage = e.Message, returnRunno = ""
}));
}
}
else
{
return(Json(new ResultDataModel {
success = false, errorMessage = "Database connect fail!", returnRunno = ""
}));
}
}
public IHttpActionResult ReceipDataAdd([FromBody] ReceiptDataModel item)
{
DBConnector.DBConnector conn = new DBConnector.DBConnector();
if (conn.OpenConnection())
{
string SQLString = @"
insert into receiptdata (documentbooknumber, documentnumber, documentdate, payername, payerid, housenumber, soi,
road, moo, tambon, amphur, province, zipcode, asreceiptto, asreceipttoremark, receiptamount, receiptpaytype,
receiptdate, receiptbank, receiptbillnumber, receiptchqbank, receiptchqnumber, receiptother, building, telephone)
values (@documentbooknumber , @documentnumber, @documentdate,
@payername, @payerid, @housenumber, @soi, @road, @moo, @tambon, @amphur, @province, @zipcode,
@asreceiptto, @asreceipttoremark, @receiptamount, @receiptpaytype, @receiptdate, @receiptbank,
@receiptbillnumber, @receiptchqbank, @receiptchqnumber, @receiptother, @building, @telephone) ";
MySqlCommand qExe = new MySqlCommand
{
Connection = conn.connection,
CommandText = SQLString
};
qExe.Parameters.AddWithValue("@documentbooknumber", item.DocumentBookNumber);
qExe.Parameters.AddWithValue("@documentnumber", item.DocumentNumber);
qExe.Parameters.AddWithValue("@documentdate", Convert.ToDateTime(item.DocumentDate, new CultureInfo("en-US")));
qExe.Parameters.AddWithValue("@payername", item.payerName);
qExe.Parameters.AddWithValue("@payerid", item.payerid);
qExe.Parameters.AddWithValue("@housenumber", item.houseNumber);
qExe.Parameters.AddWithValue("@soi", item.soi);
qExe.Parameters.AddWithValue("@road", item.road);
qExe.Parameters.AddWithValue("@moo", item.moo);
qExe.Parameters.AddWithValue("@tambon", item.tambon);
qExe.Parameters.AddWithValue("@amphur", item.amphur);
qExe.Parameters.AddWithValue("@province", item.province);
qExe.Parameters.AddWithValue("@zipcode", item.zipcode);
qExe.Parameters.AddWithValue("@asreceiptto", item.asReceiptTo);
qExe.Parameters.AddWithValue("@asreceipttoremark", item.asReceiptToRemark);
qExe.Parameters.AddWithValue("@receiptamount", item.receiptAmount);
qExe.Parameters.AddWithValue("@receiptpaytype", item.receiptPayType);
qExe.Parameters.AddWithValue("@receiptdate", Convert.ToDateTime(item.receiptDate, new CultureInfo("en-US")));
qExe.Parameters.AddWithValue("@receiptbank", item.receiptBank);
qExe.Parameters.AddWithValue("@receiptbillnumber", item.receiptBillNumber);
qExe.Parameters.AddWithValue("@receiptchqbank", item.receiptChqBank);
qExe.Parameters.AddWithValue("@receiptchqnumber", item.receiptChqNumber);
qExe.Parameters.AddWithValue("@receiptother", item.receiptOther);
qExe.Parameters.AddWithValue("@building", item.building);
qExe.Parameters.AddWithValue("@telephone", item.telephone);
qExe.ExecuteNonQuery();
long returnid = qExe.LastInsertedId;
conn.CloseConnection();
return(Ok(returnid.ToString()));
}
else
{
return(BadRequest("Database connect fail!"));
}
}
public IHttpActionResult AddMemberData([FromBody] MemberData item)
{
DBConnector.DBConnector conn = new DBConnector.DBConnector();
string SQLString;
if (conn.OpenConnection())
{
SQLString = @"INSERT INTO memberdata
(MemberId, MemberPreName, MemberName, MemberSurname, PositionNo,
BirthDate, HouseNumber, Soi, Road, Moo, Building, Tambon, Amphur,
Province, ZipCode, Telephone, MemberCitizenID)
VALUES (@MemberId, @MemberPreName, @MemberName,
@MemberSurname, @PositionNo, @BirthDate, @HouseNumber, @Soi,
@Road, @Moo, @Building, @Tambon, @Amphur, @Province, @ZipCode, @Telephone, @MemberCitizenID)";
MySqlCommand qExe = new MySqlCommand
{
Connection = conn.connection,
CommandText = SQLString
};
qExe.Parameters.AddWithValue("@MemberId", item.MemberId);
qExe.Parameters.AddWithValue("@MemberPreName", item.MemberPreName);
qExe.Parameters.AddWithValue("@MemberName", item.MemberName);
qExe.Parameters.AddWithValue("@MemberSurname", item.MemberSurname);
qExe.Parameters.AddWithValue("@PositionNo", item.PositionNo);
qExe.Parameters.AddWithValue("@BirthDate", item.BirthDate);
qExe.Parameters.AddWithValue("@HouseNumber", item.HouseNumber);
qExe.Parameters.AddWithValue("@Soi", item.Soi);
qExe.Parameters.AddWithValue("@Road", item.Road);
qExe.Parameters.AddWithValue("@Moo", item.Moo);
qExe.Parameters.AddWithValue("@Building", item.Building);
qExe.Parameters.AddWithValue("@Tambon", item.Tambon);
qExe.Parameters.AddWithValue("@Amphur", item.Amphur);
qExe.Parameters.AddWithValue("@Province", item.Province);
qExe.Parameters.AddWithValue("@ZipCode", item.ZipCode);
qExe.Parameters.AddWithValue("@Telephone", item.Telephone);
qExe.Parameters.AddWithValue("@MemberCitizenID", item.MemberCitizenID);
qExe.ExecuteNonQuery();
long returnid = qExe.LastInsertedId;
conn.CloseConnection();
return(Json(new ResultDataModel {
success = true, errorMessage = "", returnRunno = returnid.ToString()
}));
}
else
{
return(Json(new ResultDataModel {
success = false, errorMessage = "Database connect fail!", returnRunno = ""
}));
}
}
public IHttpActionResult DonateDataEdit([FromBody] MasterData item)
{
DBConnector.DBConnector conn = new DBConnector.DBConnector();
string SQLString;
if (conn.OpenConnection())
{
try
{
SQLString = @"UPDATE masterdata SET partyname = @partyname, housenumber = @housenumber, soi = @soi,
road = @road, moo = @moo, building = @building, tambon = @tambon, amphur = @amphur,
province = @province, zipcode = @zipcode, telephone = @telephone, Slogan = @Slogan, TaxID = @TaxID";
MySqlCommand qExe = new MySqlCommand
{
Connection = conn.connection,
CommandText = SQLString
};
qExe.Parameters.AddWithValue("@partyname", item.PartyName);
qExe.Parameters.AddWithValue("@housenumber", item.HouseNumber);
qExe.Parameters.AddWithValue("@soi", item.Soi);
qExe.Parameters.AddWithValue("@road", item.Road);
qExe.Parameters.AddWithValue("@moo", item.Moo);
qExe.Parameters.AddWithValue("@building", item.Building);
qExe.Parameters.AddWithValue("@tambon", item.Tambon);
qExe.Parameters.AddWithValue("@amphur", item.Amphur);
qExe.Parameters.AddWithValue("@province", item.Province);
qExe.Parameters.AddWithValue("@zipcode", item.Zipcode);
qExe.Parameters.AddWithValue("@telephone", item.Telephone);
qExe.Parameters.AddWithValue("@Slogan", item.Slogan);
qExe.Parameters.AddWithValue("@TaxID", item.TaxID);
qExe.ExecuteNonQuery();
return(Json(new ResultDataModel {
success = true, errorMessage = "", returnRunno = ""
}));
}
catch (Exception e)
{
return(Json(new ResultDataModel {
success = false, errorMessage = e.Message, returnRunno = ""
}));
}
}
else
{
return(Json(new ResultDataModel {
success = false, errorMessage = "Database connect fail!", returnRunno = ""
}));
}
}
public IHttpActionResult EditMemberData([FromBody] MemberData item)
{
DBConnector.DBConnector conn = new DBConnector.DBConnector();
string SQLString;
if (conn.OpenConnection())
{
SQLString = @"UPDATE memberdata SET MemberId = @MemberId, MemberPreName = @MemberPreName,
MemberName = @MemberName, MemberSurname = @MemberSurname, PositionNo = @PositionNo, BirthDate = @BirthDate,
HouseNumber = @HouseNumber, Soi = @Soi, Road = @Road, Moo = @Moo, Building = @Building, Tambon = @Tambon,
Amphur = @Amphur, Province = @Province, ZipCode = @ZipCode, Telephone = @Telephone,
MemberCitizenID = @MemberCitizenID WHERE MemberRunno = @MemberRunno";
MySqlCommand qExe = new MySqlCommand
{
Connection = conn.connection,
CommandText = SQLString
};
qExe.Parameters.AddWithValue("@MemberRunno", item.MemberRunno);
qExe.Parameters.AddWithValue("@MemberId", item.MemberId);
qExe.Parameters.AddWithValue("@MemberPreName", item.MemberPreName);
qExe.Parameters.AddWithValue("@MemberName", item.MemberName);
qExe.Parameters.AddWithValue("@MemberSurname", item.MemberSurname);
qExe.Parameters.AddWithValue("@PositionNo", item.PositionNo);
qExe.Parameters.AddWithValue("@BirthDate", item.BirthDate);
qExe.Parameters.AddWithValue("@HouseNumber", item.HouseNumber);
qExe.Parameters.AddWithValue("@Soi", item.Soi);
qExe.Parameters.AddWithValue("@Road", item.Road);
qExe.Parameters.AddWithValue("@Moo", item.Moo);
qExe.Parameters.AddWithValue("@Building", item.Building);
qExe.Parameters.AddWithValue("@Tambon", item.Tambon);
qExe.Parameters.AddWithValue("@Amphur", item.Amphur);
qExe.Parameters.AddWithValue("@Province", item.Province);
qExe.Parameters.AddWithValue("@ZipCode", item.ZipCode);
qExe.Parameters.AddWithValue("@Telephone", item.Telephone);
qExe.Parameters.AddWithValue("@MemberCitizenID", item.MemberCitizenID);
qExe.ExecuteNonQuery();
conn.CloseConnection();
return(Json(new ResultDataModel {
success = true, errorMessage = "", returnRunno = ""
}));
}
else
{
return(Json(new ResultDataModel {
success = false, errorMessage = "Database connect fail!", returnRunno = ""
}));
}
}
public IHttpActionResult GetMasterDatabyID(string id)
{
MasterData result = new MasterData();
DBConnector.DBConnector conn = new DBConnector.DBConnector();
string SQLString;
if (conn.OpenConnection())
{
try
{
MySqlCommand qExe = new MySqlCommand();
qExe.Connection = conn.connection;
SQLString = @"select * from masterdata";
qExe.CommandText = SQLString;
MySqlDataReader dataReader = qExe.ExecuteReader();
while (dataReader.Read())
{
result = new MasterData
{
PartyName = dataReader["partyname"].ToString(),
HouseNumber = dataReader["housenumber"].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(),
Slogan = dataReader["Slogan"].ToString(),
TaxID = dataReader["TaxID"].ToString()
};
}
return(Json(result));
}
catch (Exception e)
{
return(BadRequest(e.Message));
}
}
else
{
return(BadRequest("Database connect fail!"));
}
}
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 IHttpActionResult RunnumberDataEdit([FromBody] RunnumberDataModel item)
{
DBConnector.DBConnector conn = new DBConnector.DBConnector();
string SQLString;
if (conn.OpenConnection())
{
try
{
SQLString = @"UPDATE sy_runnumber SET rn_key = @rn_key,
rn_desc = @rn_desc, rn_format = @rn_format,
rn_runnumber = @rn_runnumber, rn_book = @rn_book,
rn_bookno = @rn_bookno WHERE rn_key = @rn_key";
MySqlCommand qExe = new MySqlCommand
{
Connection = conn.connection,
CommandText = SQLString
};
qExe.Parameters.AddWithValue("@rn_key", item.rn_key);
qExe.Parameters.AddWithValue("@rn_desc", item.rn_desc);
qExe.Parameters.AddWithValue("@rn_format", item.rn_format);
qExe.Parameters.AddWithValue("@rn_runnumber", item.rn_runnumber);
qExe.Parameters.AddWithValue("@rn_book", item.rn_book);
qExe.Parameters.AddWithValue("@rn_bookno", item.rn_bookno);
qExe.ExecuteNonQuery();
return(Json(new ResultDataModel {
success = true, errorMessage = "", returnRunno = ""
}));
}
catch (Exception e)
{
return(Json(new ResultDataModel {
success = false, errorMessage = e.Message, returnRunno = ""
}));
}
}
else
{
return(Json(new ResultDataModel {
success = false, errorMessage = "Database connect fail!", returnRunno = ""
}));
}
}
public IHttpActionResult PartyPositionEdit([FromBody] PartyPosition item)
{
List <PartyPosition> result = new List <PartyPosition>();
DBConnector.DBConnector conn = new DBConnector.DBConnector();
string SQLString;
if (conn.OpenConnection())
{
try
{
SQLString = @"update partyposition set PositionRunno = @PositionRunno, PositionNo = @PositionNo,
PositionName = @PositionName where PositionRunno = @PositionRunno";
MySqlCommand qExe = new MySqlCommand
{
Connection = conn.connection,
CommandText = SQLString
};
qExe.Parameters.AddWithValue("@PositionRunno", item.PositionRunno);
qExe.Parameters.AddWithValue("@PositionNo", item.PositionNo);
qExe.Parameters.AddWithValue("@PositionName", item.PositionName);
qExe.ExecuteNonQuery();
conn.CloseConnection();
return(Json(new ResultDataModel {
success = true, errorMessage = "", returnRunno = ""
}));
}
catch (Exception e)
{
return(Json(new ResultDataModel {
success = false, errorMessage = e.Message, returnRunno = ""
}));
}
}
else
{
return(Json(new ResultDataModel {
success = false, errorMessage = "Database connect fail!", returnRunno = ""
}));
}
}
public IHttpActionResult PreNameDelete(string id)
{
DBConnector.DBConnector conn = new DBConnector.DBConnector();
if (conn.OpenConnection())
{
try
{
string sqlString = @"delete from prename where PreNameRunno = @PreNameRunno";
MySqlCommand qExe = new MySqlCommand
{
Connection = conn.connection,
CommandText = sqlString
};
if (string.IsNullOrEmpty(id))
{
return(Json(new ResultDataModel {
success = false, errorMessage = "Key is null!", returnRunno = ""
}));
}
qExe.Parameters.AddWithValue("@PreNameRunno", id);
qExe.ExecuteNonQuery();
conn.CloseConnection();
return(Json(new ResultDataModel {
success = true, errorMessage = "", returnRunno = ""
}));
}
catch (Exception e)
{
return(Json(new ResultDataModel {
success = false, errorMessage = e.Message, returnRunno = ""
}));
}
}
else
{
return(Json(new ResultDataModel {
success = false, errorMessage = "Database connect fail!", returnRunno = ""
}));
}
}
public IHttpActionResult GetRunnumberData()
{
List <RunnumberDataModel> result = new List <RunnumberDataModel>();
DBConnector.DBConnector conn = new DBConnector.DBConnector();
string SQLString;
if (conn.OpenConnection())
{
try
{
MySqlCommand qExe = new MySqlCommand();
qExe.Connection = conn.connection;
SQLString = @"select * from sy_runnumber order by rn_key";
qExe.CommandText = SQLString;
MySqlDataReader dataReader = qExe.ExecuteReader();
while (dataReader.Read())
{
result.Add(new RunnumberDataModel
{
rn_key = dataReader["rn_key"].ToString(),
rn_desc = dataReader["rn_desc"].ToString(),
rn_format = dataReader["rn_format"].ToString(),
rn_runnumber = int.Parse(dataReader["rn_runnumber"].ToString()),
rn_book = int.Parse(dataReader["rn_book"].ToString()),
rn_bookno = int.Parse(dataReader["rn_bookno"].ToString())
});
}
return(Json(result));
}
catch (Exception e)
{
return(BadRequest(e.Message));
}
}
else
{
return(BadRequest("Database connect fail!"));
}
}
private MasterData GetMasterData()
{
MasterData result = new MasterData();
DBConnector.DBConnector conn = new DBConnector.DBConnector();
string SQLString;
if (conn.OpenConnection())
{
SQLString = @"select * from masterdata";
MySqlCommand qExe = new MySqlCommand
{
Connection = conn.connection,
CommandText = SQLString
};
MySqlDataReader dataReader = qExe.ExecuteReader();
while (dataReader.Read())
{
result = new MasterData
{
PartyName = dataReader["partyname"].ToString(),
HouseNumber = dataReader["housenumber"].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(),
Slogan = dataReader["Slogan"].ToString(),
TaxID = dataReader["TaxID"].ToString()
};
}
}
return(result);
}
public IHttpActionResult ListAllDonateType()
{
List <DonateTypeData> result = new List <DonateTypeData>();
DBConnector.DBConnector conn = new DBConnector.DBConnector();
string SQLString;
if (conn.OpenConnection())
{
try
{
SQLString = @"SELECT * FROM donatetype order by DonateTypeRunno";
MySqlCommand qExe = new MySqlCommand
{
Connection = conn.connection,
CommandText = SQLString
};
MySqlDataReader dataReader = qExe.ExecuteReader();
while (dataReader.Read())
{
DonateTypeData detail = new DonateTypeData();
detail.DonateTypeRunno = int.Parse(dataReader["DonateTypeRunno"].ToString());
detail.DonateTypeName = dataReader["DonateTypeName"].ToString();
result.Add(detail);
}
dataReader.Close();
dataReader.Dispose();
return(Json(result));
}
catch (Exception e)
{
return(BadRequest(e.Message));
}
}
else
{
return(BadRequest("Database connect fail!"));
}
}