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!"));
            }
        }
Beispiel #3
0
        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!"));
            }
        }
Beispiel #12
0
 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!"));
            }
        }
Beispiel #16
0
        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 = ""
         }));
     }
 }
Beispiel #19
0
 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!"));
            }
        }