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 getTambonData(string province, string amphur)
{
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 province, amphur, tambon, zipcode from zip_data
where province = '" + province + @"'
and amphur = '" + amphur + @"'
order by tambon";
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 = dataReader["tambon"].ToString();
detail.zipcode = dataReader["zipcode"].ToString();
result.Add(detail);
}
conn.CloseConnection();
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 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 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 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 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 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 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 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 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 PartyPositionAdd([FromBody] PartyPosition item)
{
DBConnector.DBConnector conn = new DBConnector.DBConnector();
string SQLString;
if (conn.OpenConnection())
{
try
{
SQLString = @"insert into partyposition (PositionNo, PositionName) values (@PositionNo, @PositionName) ";
MySqlCommand qExe = new MySqlCommand
{
Connection = conn.connection,
CommandText = SQLString
};
qExe.Parameters.AddWithValue("@PositionNo", item.PositionNo);
qExe.Parameters.AddWithValue("@PositionName", item.PositionName);
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 PreNameEdit([FromBody] PrenameModel item)
{
DBConnector.DBConnector conn = new DBConnector.DBConnector();
string SQLString;
if (conn.OpenConnection())
{
try
{
SQLString = @"UPDATE prename SET PreName = @PreName WHERE PreNameRunno = @PreNameRunno";
MySqlCommand qExe = new MySqlCommand
{
Connection = conn.connection,
CommandText = SQLString
};
qExe.Parameters.AddWithValue("@PreNameRunno", item.PreNameRunno);
qExe.Parameters.AddWithValue("@PreName", item.PreName);
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 ReceipDataEdit([FromBody] ReceiptDataModel item)
{
DBConnector.DBConnector conn = new DBConnector.DBConnector();
if (conn.OpenConnection())
{
string SQLString = @"
update receiptdata set documentrunno = @documentrunno,
documentbooknumber = @documentbooknumber,
documentnumber = @documentnumber,
documentdate = @documentdate,
payername = @payername,
payerid = @payerid,
housenumber = @housenumber,
soi = @soi,
road = @road,
moo = @moo,
tambon = @tambon,
amphur = @amphur,
province = @province,
zipcode = @zipcode,
asreceiptto = @asreceiptto,
asreceipttoremark = @asreceipttoremark,
receiptamount = @receiptamount,
receiptpaytype = @receiptpaytype,
receiptdate = @receiptdate,
receiptbank = @receiptbank,
receiptbillnumber = @receiptbillnumber,
receiptchqbank = @receiptchqbank,
receiptchqnumber = @receiptchqnumber,
receiptother = @receiptother,
building = @building,
telephone = @telephone 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("@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", 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(Ok());
}
else
{
return(BadRequest("Database connect fail!"));
}
}
public IHttpActionResult GenDocumentPK11(string documentRunno)
{
DBConnector.DBConnector conn = new DBConnector.DBConnector();
UtilLibs utilLibs = new UtilLibs();
if (conn.OpenConnection())
{
try
{
string SQLString = @"select docs.*, dt.DonateAssetType
from donatedetaildata docs left join donatetype dt
on docs.DonateTypeRunno = dt.DonateTypeRunno
where DocumentRunno = @DocumentRunno
and dt.DonateAssetType = '1'
and (docs.DocRefNo is null or docs.DocRefNo = '')
order by docs.DetailRunno";
MySqlCommand qExe = new MySqlCommand
{
Connection = conn.connection,
CommandText = SQLString
};
qExe.Parameters.AddWithValue("DocumentRunno", documentRunno);
MySqlDataReader detailReader = qExe.ExecuteReader();
bool CashType = false;
bool BillType = false;
bool ChqueType = false;
double DonateAmount = 0;
List <int> RunnoList = new List <int>();
while (detailReader.Read())
{
if (string.IsNullOrEmpty(detailReader["BankChqueNo"].ToString()) &&
string.IsNullOrEmpty(detailReader["Bill"].ToString()))
{
CashType = true;
}
else if (!string.IsNullOrEmpty(detailReader["BankChqueNo"].ToString()) &&
string.IsNullOrEmpty(detailReader["Bill"].ToString()))
{
ChqueType = true;
}
else if (string.IsNullOrEmpty(detailReader["BankChqueNo"].ToString()) &&
!string.IsNullOrEmpty(detailReader["Bill"].ToString()))
{
BillType = true;
}
DonateAmount = DonateAmount + double.Parse(detailReader["Amount"].ToString());
RunnoList.Add(int.Parse(detailReader["DetailRunno"].ToString()));
}
detailReader.Close();
if (CashType || ChqueType || BillType)
{
SQLString = @"select * from donatedata where DocumentRunno = @DocumentRunno";
qExe = new MySqlCommand
{
Connection = conn.connection,
CommandText = SQLString
};
qExe.Parameters.AddWithValue("DocumentRunno", documentRunno);
detailReader = qExe.ExecuteReader();
DocumentNumber docNum = new DocumentNumber();
DateTime docDate = DateTime.Now;
int DonatorRunno = -1;
while (detailReader.Read())
{
DonatorRunno = int.Parse(detailReader["DonatorRunno"].ToString());
}
detailReader.Close();
if (DonateAmount > 0 && DonatorRunno != -1)
{
docNum = utilLibs.GetDocumentNo("pk11");
SQLString = @"INSERT INTO document11 (DocumentType, DocBookNo, DocNo,
DocDate, DonatorRunno, DonateAmount, CashFlag, BillFlag, ChqueFlag)
VALUES (@DocumentType, @DocBookNo, @DocNo, @DocDate, @DonatorRunno,
@DonateAmount, @CashFlag, @BillFlag, @ChqueFlag)";
qExe = new MySqlCommand
{
Connection = conn.connection,
CommandText = SQLString
};
qExe.Parameters.AddWithValue("DocumentType", "PK11");
qExe.Parameters.AddWithValue("DocBookNo", docNum.BookNo);
qExe.Parameters.AddWithValue("DocNo", docNum.DocumentNo);
qExe.Parameters.AddWithValue("DocDate", docDate);
qExe.Parameters.AddWithValue("DonatorRunno", DonatorRunno);
qExe.Parameters.AddWithValue("DonateAmount", DonateAmount);
if (CashType)
{
qExe.Parameters.AddWithValue("CashFlag", "Y");
}
else
{
qExe.Parameters.AddWithValue("CashFlag", "N");
}
if (BillType)
{
qExe.Parameters.AddWithValue("BillFlag", "Y");
}
else
{
qExe.Parameters.AddWithValue("BillFlag", "N");
}
if (ChqueType)
{
qExe.Parameters.AddWithValue("ChqueFlag", "Y");
}
else
{
qExe.Parameters.AddWithValue("ChqueFlag", "N");
}
qExe.ExecuteNonQuery();
long returnid = qExe.LastInsertedId;
SQLString = @"update donatedetaildata set DocRefNo = @DocRefNo, DocRefBook = @DocRefBook, DocType = @DocType
where DetailRunno = @DetailRunno";
qExe = new MySqlCommand
{
Connection = conn.connection,
CommandText = SQLString
};
foreach (var runno in RunnoList)
{
qExe.Parameters.Clear();
qExe.Parameters.AddWithValue("DetailRunno", runno);
qExe.Parameters.AddWithValue("DocRefNo", docNum.DocumentNo);
qExe.Parameters.AddWithValue("DocRefBook", docNum.BookNo);
qExe.Parameters.AddWithValue("DocType", "PK11");
qExe.ExecuteNonQuery();
}
conn.CloseConnection();
return(Json(new ResultDataModel {
success = true, errorMessage = "", returnRunno = returnid.ToString()
}));
}
else
{
return(Json(new ResultDataModel {
success = false, errorMessage = "ข้อมูลไม่ถูกต้องไม่สามารถสร้างเอกสาร พ.ก. 11 ได้ หรือทำการสร้างเอกสารไปแล้ว", returnRunno = ""
}));
}
}
else
{
return(Json(new ResultDataModel {
success = true, errorMessage = "ไม่มีข้อมูลในประเภทเอกสาร พ.ก. 11 หรือทำการสร้างเอกสารไปแล้ว", 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 ReceipDataListbyRunno(string runno)
{
ReceiptDataModel result = new ReceiptDataModel();
DBConnector.DBConnector conn = new DBConnector.DBConnector();
if (conn.OpenConnection())
{
string sqlString;
if (!string.IsNullOrEmpty(runno))
{
sqlString = @"select * from receiptdata where documentrunno = @documentrunno";
}
else
{
return(Json("Document Number is blank!"));
}
MySqlCommand qExe = new MySqlCommand
{
Connection = conn.connection,
CommandText = sqlString
};
qExe.Parameters.AddWithValue("@documentrunno", runno);
MySqlDataReader dataReader = qExe.ExecuteReader();
while (dataReader.Read())
{
result.DocumentRunno = 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.payerName = dataReader["payername"].ToString();
result.payerid = dataReader["payerid"].ToString();
result.houseNumber = dataReader["housenumber"].ToString();
result.soi = dataReader["soi"].ToString();
result.road = dataReader["road"].ToString();
result.moo = dataReader["moo"].ToString();
result.tambon = dataReader["tambon"].ToString();
result.amphur = dataReader["amphur"].ToString();
result.province = dataReader["province"].ToString();
result.zipcode = dataReader["zipcode"].ToString();
result.asReceiptTo = dataReader["asreceiptto"].ToString();
result.asReceiptToRemark = dataReader["asreceipttoremark"].ToString();
result.receiptAmount = double.Parse(dataReader["receiptamount"].ToString());
result.receiptPayType = dataReader["receiptpaytype"].ToString();
result.receiptDate = Convert.ToDateTime(dataReader["receiptdate"].ToString(), new CultureInfo("en-US"));
result.receiptBank = dataReader["receiptbank"].ToString();
result.receiptBillNumber = dataReader["receiptbillnumber"].ToString();
result.receiptChqBank = dataReader["receiptchqbank"].ToString();
result.receiptChqNumber = dataReader["receiptchqnumber"].ToString();
result.receiptOther = dataReader["receiptother"].ToString();
}
dataReader.Close();
conn.CloseConnection();
return(Json(result));
}
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!"));
}
}
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!"));
}
}
