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 void ทดสอบแก้ไขPK9()
{
PK9DataController service = new PK9DataController();
ReceiptDataModel item = new ReceiptDataModel();
UtilLibs utilLibs = new UtilLibs();
DocumentNumber docNum = utilLibs.GetDocumentNo("pk9");
item.DocumentRunno = 4;
item.DocumentBookNumber = "1";
item.DocumentNumber = "AC0004";
item.DocumentDate = DateTime.Now;
item.PayerType = "1";
item.PayerRunno = 3;
item.AsReceiptTo = "ค่าบำรุงพรรคการเมืองรายปี";
item.AsReceiptToRemark = "";
item.ReceiptAmount = 6500;
item.ReceiptPayType = "";
item.ReceiptDate = DateTime.Now;
item.ReceiptBank = "";
item.ReceiptBillNumber = "";
item.ReceiptChqBank = "";
item.ReceiptChqNumber = "";
item.ReceiptOther = "";
var result = service.PK9DataEdit(item);
Assert.IsTrue(result != null);
}
public void ทดสอบบันทึกเอกสารPK9()
{
PK9DataController service = new PK9DataController();
ReceiptDataModel item = new ReceiptDataModel();
UtilLibs utilLibs = new UtilLibs();
DocumentNumber docNum = utilLibs.GetDocumentNo("pk9");
item.DocumentBookNumber = docNum.BookNo;
item.DocumentNumber = docNum.DocumentNo;
item.DocumentDate = DateTime.Now;
item.PayerType = "2";
item.PayerRunno = 1;
item.AsReceiptTo = "ค่าจัดกิจกรรมระดมทุน";
item.AsReceiptToRemark = "";
item.ReceiptAmount = 1750;
item.ReceiptPayType = "";
item.ReceiptDate = DateTime.Now;
item.ReceiptBank = "";
item.ReceiptBillNumber = "";
item.ReceiptChqBank = "";
item.ReceiptChqNumber = "";
item.ReceiptOther = "";
var result = service.PK9DataAdd(item);
Assert.IsTrue(result != null);
}
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 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!"));
}
}
public IHttpActionResult GetDocPK12ListAll()
{
List <PK12Model> result = new List <PK12Model>();
UtilLibs utilLibs = new UtilLibs();
DBConnector.DBConnector conn = new DBConnector.DBConnector();
string SQLString;
if (conn.OpenConnection())
{
try
{
MasterData masterData = GetMasterData();
SQLString = @"select doc.*, dt.*
from document12 doc left join donatordata dt on doc.DonatorRunno = dt.DonatorRunno
order by DocBookNo, DocNo";
MySqlCommand qExe = new MySqlCommand
{
Connection = conn.connection,
CommandText = SQLString
};
MySqlDataReader dataReader = qExe.ExecuteReader();
while (dataReader.Read())
{
PK12Model row = new PK12Model();
row.DocumentRunno = int.Parse(dataReader["DocumentRunno"].ToString());
row.PartyName = masterData.PartyName;
row.PartyTel = masterData.Telephone;
row.PartyTaxID = "เลขประจำตัวผู้เสียภาษี " + masterData.TaxID;
row.PartyAddr1 = GetAddrRow1(masterData);
row.PartyAddr2 = GetAddrRow2(masterData);
row.DocBookNo = dataReader["DocBookNo"].ToString();
row.DocNum = dataReader["DocNo"].ToString();
row.DocDate = DateTime.Parse(dataReader["DocDate"].ToString());
row.DocDateStr = DateTime.Parse(dataReader["DocDate"].ToString()).ToString("dd/MM/yyyy");
row.DonatorName = (dataReader["DonatorPreName"].ToString() + dataReader["DonatorName"].ToString() + " " + dataReader["DonatorSurName"].ToString()).Trim();
row.CitizenID = dataReader["DonatorCitizenID"].ToString();
row.RegisterID = dataReader["DonatorregisterNo"].ToString();
row.TaxID = dataReader["DonatorTaxId"].ToString();
row.HouseNum = dataReader["HouseNumber"].ToString();
row.Moo = dataReader["Moo"].ToString();
row.Building = dataReader["Building"].ToString();
row.Soi = dataReader["Soi"].ToString();
row.Road = dataReader["Road"].ToString();
row.Tambon = dataReader["Tambon"].ToString();
row.Amphur = dataReader["Amphur"].ToString();
row.Province = dataReader["Province"].ToString();
row.Zipcode = dataReader["Zipcode"].ToString();
row.Telephone = dataReader["Telephone"].ToString();
row.AssetFlag = dataReader["AssetFlag"].ToString();
row.AssetAmount = double.Parse(dataReader["AssetAmount"].ToString());
row.AssetAmountStr = utilLibs.ThaiBaht(dataReader["AssetAmount"].ToString());
row.AssetDesc = dataReader["AssetDesc"].ToString();
row.BenefitFlag = dataReader["BenefitFlag"].ToString();
row.BenefitAmount = double.Parse(dataReader["BenefitAmount"].ToString());
row.BenefitAmountStr = utilLibs.ThaiBaht(dataReader["BenefitAmount"].ToString());
row.BenefitDesc = dataReader["BenefitDesc"].ToString();
row.Amount = double.Parse(dataReader["DonateAmount"].ToString());
row.AmountStr = utilLibs.ThaiBaht(dataReader["DonateAmount"].ToString());
result.Add(row);
}
dataReader.Close();
dataReader.Dispose();
return(Json(result));
}
catch (Exception e)
{
return(BadRequest(e.Message));
}
}
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 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);
}
}
