using System;
using System.Data;
using System.Data.Sql;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace Transport.DAL
{
    class dal_commonCrud
    {
        
        
        public static bool RunQuery(string SQLQuery)
        {
            //bool ActionResult = false;
            try
            {
                SqlConnection con = new SqlConnection(dal_config.connection_string);
                SqlCommand com = new SqlCommand();
                com.CommandType = CommandType.Text;
                com.CommandText = SQLQuery;
                com.Connection = con;

                if (con.State != ConnectionState.Open)
                {
                    con.Open();
                }

                int result = com.ExecuteNonQuery();

                if (con.State != ConnectionState.Closed)
                {
                    con.Close();
                }

                if (result > 0)
                {
                    //ActionResult = true;
                     return true;
                }
                else
                {
                    //ActionResult = false;
                    return false;
                }
            }
            catch (Exception e)
            {
                MessageBox.Show(e.ToString());
                return false;
            }
            //return ActionResult;
        }

        public static DataTable SelectAllRecords(string TableName)
        {
            string SQLQuery="";

            if (TableName == "tbl_transport_in")
            {
              
                //فیلد اول این کوئری شماره ردیف رو تولید میکنه 
                SQLQuery = "SELECT * FROM view_select_all_transport_in";
            }
            if (TableName == "tbl_transport_out")
            {
                SQLQuery = "SELECT * FROM view_select_all_transport_out";
            }
              if (TableName == "tbl_transport")
              {
                  SQLQuery = "SELECT * FROM view_select_all_transport";
              }

            if (TableName == "tbl_transport_fast_search")
            {
                SQLQuery = "SELECT transport_id, (transport_type) WHEN '1' THEN 'ورودی' ELSE 'خروجی' END as transport_type, CASE (peyment_type) WHEN '1' THEN 'نقدی' ELSE 'پس کرایه' END AS peyment_type,tbl_transport.company_id,company_name,tbl_city.city_id,tbl_city.city_name,transport_number,transport_date,transport_weight,transport_total_price,transport_price,agency_portion,transport_description,is_city_agency,current_agency_portion_price " +
                                    "FROM (tbl_transport LEFT JOIN tbl_company ON tbl_company.company_id = tbl_transport.company_id) LEFT JOIN tbl_city ON  tbl_city.city_id = tbl_transport.city_id " +
                                    " WHERE transport_id like N'%{0}%' OR transport_type like N'%{0}%' OR peyment_type like N'%{0}%' OR company_name like N'%{0}%' OR transport_number like N'%{0}%' OR transport_date like N'%{0}%' OR transport_weight like N'%{0}%' OR transport_total_price like N'%{0}%' OR transport_price like N'%{0}%' OR agency_portion like N'%{0}%' OR transport_description like N'%{0}%'";
            }

            if (TableName == "tbl_company")
            {
                SQLQuery = "SELECT tbl_company.company_id, tbl_city.city_id,tbl_city.city_name, tbl_company.company_name, tbl_company.company_address, tbl_company.company_tel1, tbl_company.company_tel2, tbl_company.company_max_fix_weight, tbl_company.company_base_price, tbl_company.company_addition_price, tbl_company.company_agency_portion,tbl_company.company_max_fix_weight2, tbl_company.company_base_price2, tbl_company.company_addition_price2, tbl_company.company_agency_portion2"
                                   +" FROM tbl_city INNER JOIN tbl_company ON tbl_city.city_id = tbl_company.company_city_id;";
            }

            if (TableName == "tbl_cost")
            {
                SQLQuery = "SELECT tbl_cost.cost_group_id, tbl_cost_group.cost_group_name, tbl_cost.cost_id, tbl_cost.cost_price, tbl_cost.cost_description, tbl_cost.cost_date "+
                                    " FROM tbl_cost_group INNER JOIN tbl_cost ON tbl_cost_group.cost_group_id = tbl_cost.cost_group_id;";
            }

            if (TableName == "tbl_city")
            {
                SQLQuery = "SELECT * FROM tbl_city";
            }

            if (TableName == "tbl_cost_group")
            {
                SQLQuery = "SELECT * FROM tbl_cost_group";
            }

            try
            {

                SqlConnection con = new SqlConnection(dal_config.connection_string);
                SqlCommand com = new SqlCommand();
                com.CommandType = CommandType.Text;
                com.CommandText = SQLQuery;
                com.Connection = con;

                SqlDataAdapter DA = new SqlDataAdapter(com);
                DataSet dataset = new DataSet();

                DA.Fill(dataset, "ExportedTable");

                return dataset.Tables[0];


            }
            catch (Exception e)
            {
                MessageBox.Show(e.ToString());
                return null;
            }
        }

        
        public static DataTable search(string SQLQuery)
        {
            try
            {
                SqlConnection con = new SqlConnection(dal_config.connection_string);
                SqlCommand com = new SqlCommand(SQLQuery, con);
                SqlDataAdapter adapter = new SqlDataAdapter(com);
                DataSet ds = new DataSet();
                adapter.Fill(ds, "SearchResult");
                DataTable dtContact = ds.Tables[0];
                return dtContact;
            }
            catch (Exception e)
            {
                MessageBox.Show(e.ToString());
                
            }
            DataTable dtt = null;
            return dtt;
        }

        public static bool checkRecordExists(string targetTable, string fieldName, string fieldValue)
        {
            string querystring = string.Format("SELECT * FROM {0} Where {1}={2}", targetTable, fieldName, fieldValue);
            SqlConnection con = new SqlConnection(dal_config.connection_string);
            SqlCommand com = new SqlCommand(querystring, con);
            SqlDataAdapter adapter = new SqlDataAdapter(com);
            DataSet ds = new DataSet();
            adapter.Fill(ds, "SearchResult");
           

            if (ds.Tables["SearchResult"].Rows.Count>0)
            {
                return true;
            }
            else
            {
                return false;
            }

        }


    }
}
