Example #1
0
        public override List<Com.Martin.SMS.Data.SMSOutgoing> Execute() {
            List<SMS.Data.SMSOutgoing> lst = new List<Com.Martin.SMS.Data.SMSOutgoing>();

            MySqlConnection conn = new MySqlConnection(CommandHelper.ConnectionString);
            try {
                conn.Open();
                MySqlCommand command = conn.CreateCommand();
                command.CommandText = "SELECT n.id_news, n.news, a.cp_mobile_number FROM catering.news_broadcast n, catering.customer_profile a where n.status='NOK'";

                Decimal id_news = -1;
                MySqlDataReader reader = command.ExecuteReader();
                while (reader.Read()) {
                    SMS.Data.SMSOutgoing outgoing = new Com.Martin.SMS.Data.SMSOutgoing();

                    if (!reader.IsDBNull(0))
                        id_news = reader.GetDecimal(0);
                    if (!reader.IsDBNull(1))
                        outgoing.MessageText = reader.GetString(1);
                    if (!reader.IsDBNull(2))
                        outgoing.DestinationNo = reader.GetString(2);

                    outgoing.DateProcess = DateTime.Now;
                    outgoing.RegisterType = "BRC";
                    outgoing.RegisterName = "News";
                    outgoing.Type = Com.Martin.SMS.Data.SMSType.Broadcast;
                    lst.Add(outgoing);
                }
                reader.Close();

                if (id_news != -1) {
                    command.CommandText = "Update news_broadcast set status='OK' where id_news = ?id_news";
                    command.Parameters.Clear();
                    command.Parameters.AddWithValue("id_news", id_news);
                    command.ExecuteNonQuery();
                }

            } finally {
                conn.Close();
            }

            return lst;
        }
Example #2
0
        public override Com.Martin.SMS.Data.SMSOutgoing Execute() {
            MySqlConnection conn = new MySqlConnection(CommandHelper.ConnectionString);
            SMS.Data.SMSOutgoing outgoing = new Com.Martin.SMS.Data.SMSOutgoing();
            try {
                String orderDate = this.Parameters.Get(0);

                conn.Open();
                MySqlCommand command = conn.CreateCommand();
                command.CommandText = "SELECT (select m_name from menu b where b.menu_id = m.ms_menu_a) as Menu_A , ";
                command.CommandText += "  (select m_name from menu b where b.menu_id = m.ms_menu_b) as Menu_B, ";
                command.CommandText += "  (select m_name from menu b where b.menu_id = m.ms_menu_c) as Menu_C ";
                command.CommandText += " FROM catering.menu_schedule m where date_format(ms_date, '%Y-%m-%d')=?schedule";
                command.Parameters.Clear();
                command.Parameters.AddWithValue("?schedule", orderDate);

                String teks = "";
                MySqlDataReader reader = command.ExecuteReader();
                if (reader.Read()) {
                    teks = "Menu tgl " + orderDate + " sbb, A:" + reader.GetString(0) + ",";
                    teks += " B:" + reader.GetString(1) + ",";
                    teks += " C:" + reader.GetString(2);
                }
                reader.Close();

                outgoing.DestinationNo = this.RequestSMS.Sender;
                outgoing.RegisterType = this.GetRegType();
                outgoing.RegisterName = this.GetRegName();
                outgoing.Type = Com.Martin.SMS.Data.SMSType.RequestResponse;
                outgoing.SMSRequest = this.RequestSMS;
                outgoing.DateProcess = DateTime.Now;
                outgoing.MessageText = teks;
            } finally {
                conn.Close();
            }
            return outgoing; ;
        }
Example #3
0
        public override Com.Martin.SMS.Data.SMSOutgoing Execute() {
            MySqlConnection conn = new MySqlConnection(CommandHelper.ConnectionString);
            SMS.Data.SMSOutgoing outgoing = new Com.Martin.SMS.Data.SMSOutgoing();
            try {
                String scheduleDate = this.Parameters.Get(0);
                String menuA = this.Parameters.Get(1);
                String menuB = this.Parameters.Get(2);
                String menuC = this.Parameters.Get(3);

                conn.Open();
                MySqlCommand command = conn.CreateCommand();
                command.CommandText = "insert into menu_schedule(ms_date, ms_menu_a, ms_menu_b, ms_menu_c) values (?schedule, ?menuA, ?menuB, ?menuC)";
                command.Parameters.Clear();
                command.Parameters.AddWithValue("?schedule", scheduleDate);
                command.Parameters.AddWithValue("?menuA", menuA);
                command.Parameters.AddWithValue("?menuB", menuB);
                command.Parameters.AddWithValue("?menuC", menuC);
                command.ExecuteNonQuery();

                MySqlCommand cmdCreate = conn.CreateCommand();
                cmdCreate.CommandText = "insert into customer_order(menu_schedule_id, com_order_date, com_selected, com_order_status, customer_id, id_input)";
                cmdCreate.CommandText += "select a.Menu_Schedule_Id, a.MS_DATE, a.MS_MENU_A, 'ACTIVE', b.Customer_ID, 0  from menu_schedule a join customer_profile b where a.menu_schedule_id not in (select c.menu_schedule_id from customer_order c)";
                cmdCreate.ExecuteNonQuery();

                // harusnya ada trigger create schedule buat customer tp ini dipikir nantilah
                outgoing.DestinationNo = this.RequestSMS.Sender;
                outgoing.RegisterType = this.GetRegType();
                outgoing.RegisterName = this.GetRegName();
                outgoing.Type = Com.Martin.SMS.Data.SMSType.RequestResponse;
                outgoing.SMSRequest = this.RequestSMS;
                outgoing.DateProcess = DateTime.Now;
                outgoing.MessageText = "Jadwal menu berhasil disimpan.";
            } finally {
                conn.Close();
            }
            return outgoing; ;
        }
Example #4
0
        public override Com.Martin.SMS.Data.SMSOutgoing Execute() {
            MySqlConnection conn = new MySqlConnection(CommandHelper.ConnectionString);
            SMS.Data.SMSOutgoing outgoing = new Com.Martin.SMS.Data.SMSOutgoing();
            try {
                String news = this.Parameters.Get(0);

                conn.Open();
                MySqlCommand command = conn.CreateCommand();
                command.CommandText = "insert into news_broadcast(news_date, news, status) values (?date, ?news, 'nok')";
                command.Parameters.Clear();
                command.Parameters.AddWithValue("?date", DateTime.Now.ToString("yyyyMMdd"));
                command.Parameters.AddWithValue("?news", news);
                command.ExecuteNonQuery();

                outgoing.DestinationNo = this.RequestSMS.Sender;
                outgoing.RegisterType = this.GetRegType();
                outgoing.RegisterName = this.GetRegName();
                outgoing.Type = Com.Martin.SMS.Data.SMSType.RequestResponse;
                outgoing.SMSRequest = this.RequestSMS;
                outgoing.DateProcess = DateTime.Now;
                outgoing.MessageText = "Berita berhasil ditambah, akan dikirim pada jadwal berikutnya.";

            } finally {
                conn.Close();
            }
            return outgoing; ;
        }
Example #5
0
        public override Com.Martin.SMS.Data.SMSOutgoing Execute() {
            MySqlConnection conn = new MySqlConnection(CommandHelper.ConnectionString);
            SMS.Data.SMSOutgoing outgoing = new Com.Martin.SMS.Data.SMSOutgoing();
            try {
                String customer = this.Parameters.Get(0);
                String kolom = this.Parameters.Get(1);
                String nilai = this.Parameters.Get(2);

                conn.Open();
                MySqlCommand command = conn.CreateCommand();
                command.CommandText = "update customer_profile set " + kolom + " = ?value where customer_id=?customer";
                command.Parameters.Clear();
                command.Parameters.AddWithValue("?value", nilai);
                command.Parameters.AddWithValue("?customer", customer);
                
                int iresult = command.ExecuteNonQuery();

                outgoing.DestinationNo = this.RequestSMS.Sender;
                outgoing.RegisterType = this.GetRegType();
                outgoing.RegisterName = this.GetRegName();
                outgoing.Type = Com.Martin.SMS.Data.SMSType.RequestResponse;
                outgoing.SMSRequest = this.RequestSMS;
                outgoing.DateProcess = DateTime.Now;
                if (iresult <= 0) {
                    outgoing.MessageText = "Profile anda gagal diupdate, data terlalu panjang atau custID tidak ada";
                } else {
                    outgoing.MessageText = "Profile anda telah berhasil diupdate";
                }
            } finally {
                conn.Close();
            }
            return outgoing; ;
        }
Example #6
0
        public override Com.Martin.SMS.Data.SMSOutgoing Execute() {
            MySqlConnection conn = new MySqlConnection(CommandHelper.ConnectionString);
            SMS.Data.SMSOutgoing outgoing = new Com.Martin.SMS.Data.SMSOutgoing();
            try {
                string menuName = this.Parameters.Get(0);
                string menuType = this.Parameters.Get(1);
                string kategori = this.Parameters.Get(2);
                decimal price = Decimal.Parse(this.Parameters.Get(3));
                string menuID = this.Parameters.Get(4);

                conn.Open();
                MySqlCommand command = conn.CreateCommand();
                command.CommandText = "update menu set m_name=?name, m_type=?type, m_category=?category, m_price=?price where menu_id=?id";
                command.Parameters.Clear();
                command.Parameters.AddWithValue("?name", menuName);
                command.Parameters.AddWithValue("?type", menuType);
                command.Parameters.AddWithValue("?category", kategori);
                command.Parameters.AddWithValue("?price", price);
                command.Parameters.AddWithValue("?id", menuID);
                command.ExecuteNonQuery();

                outgoing.DestinationNo = this.RequestSMS.Sender;
                outgoing.RegisterType = this.GetRegType();
                outgoing.RegisterName = this.GetRegName();
                outgoing.Type = Com.Martin.SMS.Data.SMSType.RequestResponse;
                outgoing.SMSRequest = this.RequestSMS;
                outgoing.DateProcess = DateTime.Now;
                outgoing.MessageText = "menu berhasil diperbarui";
            } finally {
                conn.Close();
            }
            return outgoing; ;
        }
Example #7
0
        public override Com.Martin.SMS.Data.SMSOutgoing Execute() {
            MySqlConnection conn = new MySqlConnection(CommandHelper.ConnectionString);
            SMS.Data.SMSOutgoing outgoing = new Com.Martin.SMS.Data.SMSOutgoing();
            try {
                String customer = this.Parameters.Get(0);
                String typeBill = this.Parameters.Get(1);
                int month = 0;
                switch (typeBill) {
                    case "LAST":
                        month = DateTime.Now.Month - 1;
                        break;
                    case "CURRENT":
                        month = DateTime.Now.Month;
                        break;
                }
                if (month == 00) 
                    month = 12;
                conn.Open();

                // Customer Order
                MySqlCommand command = conn.CreateCommand();
                command.CommandText = "select sum(price) from (SELECT customer_id, (select m_price from menu b where b.menu_id = c.com_selected) price FROM customer_order c where c.customer_id=?customer and month(c.com_order_date) = ?bln ) d group by d.customer_id";
                command.Parameters.Clear();
                command.Parameters.AddWithValue("?customer", customer);
                command.Parameters.AddWithValue("?bln", month);
                decimal master = (decimal)command.ExecuteScalar();

                command = conn.CreateCommand();
                command.CommandText = "SELECT sum(price) FROM customer_order_detail c where c.menu_schedule_customer_s in (select d.menu_schedule_customer_s from customer_order d where d.customer_id = ?customer and month(d.com_order_date) = ?bln)";
                command.Parameters.Clear();
                command.Parameters.AddWithValue("?customer", customer);
                command.Parameters.AddWithValue("?bln", month);
                decimal detail = (decimal)command.ExecuteScalar();

                decimal total = master + detail;

                String teks = "tagihan anda untuk periode " + month.ToString() + "-" + DateTime.Now.Year.ToString() + " adalah sebesar " + total.ToString();

                outgoing.DestinationNo = this.RequestSMS.Sender;
                outgoing.RegisterType = this.GetRegType();
                outgoing.RegisterName = this.GetRegName();
                outgoing.Type = Com.Martin.SMS.Data.SMSType.RequestResponse;
                outgoing.SMSRequest = this.RequestSMS;
                outgoing.DateProcess = DateTime.Now;
                outgoing.MessageText = teks;
            } finally {
                conn.Close();
            }
            return outgoing; ;
        }
Example #8
0
        public override Com.Martin.SMS.Data.SMSOutgoing Execute() {
            MySqlConnection conn = new MySqlConnection(CommandHelper.ConnectionString);
            SMS.Data.SMSOutgoing outgoing = new Com.Martin.SMS.Data.SMSOutgoing();
            try {
                String orderDate = this.Parameters.Get(0);
                String customer = this.Parameters.Get(1);
                String menuID = this.Parameters.Get(2);

                conn.Open();

                MySqlCommand command = conn.CreateCommand();
                command.CommandText = "select m_price from menu where menu_id=?menu";
                command.Parameters.Clear();
                command.Parameters.AddWithValue("?menu", menuID);
                decimal price = (decimal)command.ExecuteScalar();

                command.CommandText = "select menu_schedule_customer_s from customer_order where date_format(com_order_date, '%Y-%m-%d')=?order and customer_id=?customer";
                command.Parameters.Clear();
                command.Parameters.AddWithValue("?order", orderDate);
                command.Parameters.AddWithValue("?customer", customer);
                Int32 orderID = (Int32)command.ExecuteScalar();

                command.CommandText = "insert into customer_order_detail(menu_id, price, menu_schedule_customer_s, id_input)";
                command.CommandText += " values(?menu, ?price, ?schedule, ?input)";
                command.Parameters.Clear();
                command.Parameters.AddWithValue("?menu", menuID);
                command.Parameters.AddWithValue("?price", price);
                command.Parameters.AddWithValue("?schedule", orderID);
                command.Parameters.AddWithValue("?input", this.RequestSMS.ID);
                command.ExecuteNonQuery();

                outgoing.DestinationNo = this.RequestSMS.Sender;
                outgoing.RegisterType = this.GetRegType();
                outgoing.RegisterName = this.GetRegName();
                outgoing.Type = Com.Martin.SMS.Data.SMSType.RequestResponse;
                outgoing.SMSRequest = this.RequestSMS;
                outgoing.DateProcess = DateTime.Now;
                outgoing.MessageText = "Pesanan anda telah berhasil diproses.";

            } finally {
                conn.Close();
            }
            return outgoing; ;
        }
Example #9
0
        public override Com.Martin.SMS.Data.SMSOutgoing Execute() {
            MySqlConnection conn = new MySqlConnection(CommandHelper.ConnectionString);
            SMS.Data.SMSOutgoing outgoing = new Com.Martin.SMS.Data.SMSOutgoing();
            try {
                String customer = this.Parameters.Get(0);
                String orderDate = this.Parameters.Get(1);

                conn.Open();

                MySqlCommand command = conn.CreateCommand();
                command.CommandText = "UPDATE customer_order set com_order_status = 'CANCEL' where date_format(com_order_date, '%Y-%m-%d') = ?orderDate and customer_id = ?customer";
                command.Parameters.Clear();
                command.Parameters.AddWithValue("?orderDate", orderDate);
                command.Parameters.AddWithValue("?customer", customer);
                command.ExecuteNonQuery();

                outgoing.DestinationNo = this.RequestSMS.Sender;
                outgoing.RegisterType = this.GetRegType();
                outgoing.RegisterName = this.GetRegName();
                outgoing.Type = Com.Martin.SMS.Data.SMSType.RequestResponse;
                outgoing.SMSRequest = this.RequestSMS;
                outgoing.DateProcess = DateTime.Now;
                outgoing.MessageText = "Anda telah membatalkan menu tgl " + orderDate;
            } finally {
                conn.Close();
            }
            return outgoing; ;
        }
Example #10
0
        public override Com.Martin.SMS.Data.SMSOutgoing Execute() {
            MySqlConnection conn = new MySqlConnection(CommandHelper.ConnectionString);
            SMS.Data.SMSOutgoing outgoing = new Com.Martin.SMS.Data.SMSOutgoing();
            try {
                conn.Open();
                // Get Parameters
                String custID = this.Parameters.Get(0);
                String orderDate = this.Parameters.Get(1);
                String menuSelect = this.Parameters.Get(2);
                decimal menuID = 0;
                decimal ScheduleID = 0;

                MySqlCommand command = conn.CreateCommand();
                command.CommandText = "select Menu_Schedule_Id, Ms_Date, Ms_Menu_A, Ms_Menu_B, Ms_Menu_C from Menu_Schedule where date_format(ms_date, '%Y-%m-%d') = ?Ms_Date";
                command.Parameters.Clear();
                command.Parameters.AddWithValue("?Ms_Date", orderDate);
                MySqlDataReader reader = command.ExecuteReader();
                if (reader.Read()) {
                    ScheduleID = menuID = reader.GetDecimal(0);
                    switch (menuSelect) {
                        case "A":
                            menuID = reader.GetDecimal(2);
                            break;
                        case "B":
                            menuID = reader.GetDecimal(3);
                            break;
                        case "C":
                            menuID = reader.GetDecimal(4);
                            break;
                    }
                }
                reader.Close();

                command.CommandText = "update customer_order set Com_Selected = ?comSelected, Id_Input = ?input where menu_schedule_id = ?schedule and customer_id = ?customer";
                command.Parameters.Clear();
                command.Parameters.AddWithValue("?comSelected", menuID);
                command.Parameters.AddWithValue("?input", this.RequestSMS.ID);
                command.Parameters.AddWithValue("?schedule", ScheduleID);
                command.Parameters.AddWithValue("?customer", custID);
                command.ExecuteNonQuery();

                outgoing.DestinationNo = this.RequestSMS.Sender;
                outgoing.RegisterType = this.GetRegType();
                outgoing.RegisterName = this.GetRegName();
                outgoing.Type = Com.Martin.SMS.Data.SMSType.RequestResponse;
                outgoing.SMSRequest = this.RequestSMS;
                outgoing.DateProcess = DateTime.Now;
                outgoing.MessageText = "Anda telah merubah menu tgl " + orderDate + " menjadi menu " + menuSelect;
            } finally {
                conn.Close();
            }
            return outgoing;
        }
Example #11
0
        public override Com.Martin.SMS.Data.SMSOutgoing Execute() {
            MySqlConnection conn = new MySqlConnection(CommandHelper.ConnectionString);
            SMS.Data.SMSOutgoing outgoing = new Com.Martin.SMS.Data.SMSOutgoing();
            try {
                String kategori = this.Parameters.Get(0);

                conn.Open();
                MySqlCommand command = conn.CreateCommand();
                command.CommandText = "select menu_id, m_name from menu where m_type='ADDT' and m_category=?category limit 5 offset 1";
                command.Parameters.Clear();
                command.Parameters.AddWithValue("?category", kategori);

                String teks = "Menu Tambahan (kode-nama):";
                MySqlDataReader reader = command.ExecuteReader();
                while (reader.Read()) {
                    teks += reader.GetString(0) + "-" + reader.GetString(1) + ",";
                }
                reader.Close();
                teks = teks.Substring(0, teks.Length - 1);

                outgoing.DestinationNo = this.RequestSMS.Sender;
                outgoing.RegisterType = this.GetRegType();
                outgoing.RegisterName = this.GetRegName();
                outgoing.Type = Com.Martin.SMS.Data.SMSType.RequestResponse;
                outgoing.SMSRequest = this.RequestSMS;
                outgoing.DateProcess = DateTime.Now;
                outgoing.MessageText = teks;
            } finally {
                conn.Close();
            }
            return outgoing; ;
        }