using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using property.model.Warrant;
using property.model.GetConnection;
using System.Windows.Forms;

namespace property.control.warrantInfo
{
    class GetWarInfo
    {
        SqlConnection con = null;
        SqlCommand cmd = null;
        GetConnection getcon = new GetConnection();
        wait war = new wait();
        #region //檢查表中是否有記錄   public int GetWarrantInfo()
        public int GetWarrantInfo()
        {
            int P_int_result;
            try
            {
                con = getcon.GetCon();
                cmd = new SqlCommand();
                string P_str_select = "select count(*) from tb_warrantInfo";
                cmd.CommandText = P_str_select;
                cmd.Connection = con;
                int P_int_Select = (int)cmd.ExecuteScalar();
                // int t = com.ExecuteNonQuery();
                cmd.Connection.Close();
                con.Close();
                if (P_int_Select != 0) //表示有
                {
                    P_int_result = 1;
                }
                else
                {
                    P_int_result = 0;//表示沒有
                }//end block else if 
                return P_int_result;

            }
            catch (Exception e)
            {

                P_int_result = 2;//表示錯誤
                return P_int_result;

            }//end block 

        
        }// end block GetWarrantInfo()
        #endregion
        #region //自動編號   public int GetWarrantID()
        public int GetWarrantID()
        {
            int P_int_result = this.GetWarrantInfo();//查找表中是否有記錄
            try
            {
                if (P_int_result == 1)// 表示表中以有記錄
                {
                    con = getcon.GetCon();
                    cmd = new SqlCommand();
                    string P_str_select = "select max(warrantId) from tb_warrantInfo";
                    cmd.CommandText = P_str_select;
                    cmd.Connection = con;
                    SqlDataReader dr = cmd.ExecuteReader();
                    while (dr.Read())
                    {
                        P_int_result = Convert.ToInt32(dr[0].ToString()) + 1;

                    }// end block wheile
                    dr.Close();
                    cmd.Connection.Close();
                    con.Close();

                    //  }// end block if 
                }// end block 
                if (P_int_result == 0)// 表示表中沒有記錄
                { P_int_result = 1001; }// end blokc 
                return P_int_result;

            }// end block try
            catch (Exception e)
            {
                P_int_result = 0;
                return P_int_result;
            }// end blokc catch
        
        }// end block GetWarrantID()
        #endregion
        #region//查找編號是否重復  public string checkwarrid(Warrant war)
        public string checkwarrid(Warrant war)
        {


            string P_str_result = null;
            // int P_int_select=this.subjectInsert\
            try
            {
                int P_int_select = this.GetWarrantInfo(); //查找表是否有記錄
                if (P_int_select == 1)//表示表中有記錄可以查找是否重復
                {
                    con = getcon.GetCon();
                    cmd = new SqlCommand();
                    string P_str_select = "select count(*) from tb_warrantInfo where warrantId='" + war.getP_int_warrantId()+ "'";
                    cmd.CommandText = P_str_select;
                    cmd.Connection = con;
                    int P_int_Select = (int)cmd.ExecuteScalar();
                    // int t = com.ExecuteNonQuery();
                    cmd.Connection.Close();
                    con.Close();
                    if (P_int_Select == 1)
                    {
                        P_str_result = "OK";
                    }
                    else
                    {
                        P_str_result = "NO";
                    }//end block else if 

                }// end block if 
                if (P_int_select == 0)//表示表中沒有要找的記錄此編號可用記錄，此時只能是第一次添加
                { P_str_result = "NO"; }
                return P_str_result;
            }
            catch (Exception e)
            {

                //  result = "Error";
                return e.ToString();

            }//end block 
        
        }// end block if checkwarrid()
        #endregion
        #region//添加憑證信息    public string getwarrantAdd(Warrant war)
        public string getwarrantAdd(Warrant war)
        {
            string P_str_result = null;
            try
            {
                    con = getcon.GetCon();
                    cmd = new SqlCommand();
                    string P_str_insert = "insert into tb_warrantInfo (warrantId,warrantDate,subId,subLendmoney,subGoodsMoney )  values('"+war.getP_int_warrantId()+"','"+war.getP_date_warrantDate()+"','"+war.getP_str_subId()+"','"+war.getP_int_subLendmoney()+"','"+war.getP_int_subGoodsMoney()+"')";
                    cmd.CommandText = P_str_insert;
                    cmd.Connection = con;
                    int P_int_inst = cmd.ExecuteNonQuery();
                    if (P_int_inst == 1)
                    {
                        P_str_result = "insetOk";
                    }
                    else
                    {
                        P_str_result = "insetNo";
                    }// end blcok if 
                    cmd.Connection.Close();
                    con.Close();
              
                return P_str_result;
            }// end block try
            catch (Exception e)
            {
                P_str_result = "addError";
                return P_str_result;
            }// end blokc catch

        
        }// end block getwarrantAdd()
        #endregion
        #region//修改信息  public string GetUpdateWarrt(Warrant war)
        public string GetUpdateWarrt(wait war)
        {
            string P_str_result = null;
            string P_str_update=null;
            try
            {
                con = getcon.GetCon();
                cmd = new SqlCommand();
                if (war.getP_int_subLendmoney().ToString() == "0")
                { P_str_update = "update tb_warrantInfo set subGoodsMoney='" + war.getP_int_subGoodsMoney() + "' where warrantId='" + war.getP_int_warrantId() + "'"; }
                if (war.getP_int_subGoodsMoney().ToString() == "0")
                { P_str_update = "update tb_warrantInfo set subLendmoney='" + war.getP_int_subLendmoney() + "' where warrantId='" + war.getP_int_warrantId() + "'"; }
                cmd.CommandText = P_str_update;
                cmd.Connection = con;
                int P_int_inst = cmd.ExecuteNonQuery();
                if (P_int_inst == 1)
                {
                    P_str_result = "updateOk";
                }
                else 
                {
                    P_str_result = "updateNO";
                }// end blcok if 
                cmd.Connection.Close();
                con.Close();
                return P_str_result;
            }// end block try
            catch (Exception e)
            {
                P_str_result = "updateError";
                return P_str_result;
            }// end blokc catch
        }
        #endregion
        #region//幫定listView1控件有條件的  public void GedListView(Object list)
        public void GedListView(Object list)
        {
            if (list.GetType().ToString() == "System.Windows.Forms.ListView")
            {
                ListView lv = (ListView)list;//p實例對象
                con=getcon.GetCon();
                cmd=new SqlCommand();
                string P_str_select = "select b.warrantId,b.warrantDate,a.subName,b.subLendmoney a ,b.subGoodsMoney ,b.subRemark from tb_subjectInfo a,tb_warrantInfo b where (a.subId=b.subid)and b.subRemark is null ";
                cmd.CommandText=P_str_select;
                cmd.Connection=con;
                SqlDataReader dr = cmd.ExecuteReader();
                lv.Items.Clear();//清空
                lv.View = View.Details;//顯示示圖
                while (dr.Read())
                {
                    ListViewItem lt = new ListViewItem(dr.GetValue(0).ToString());
                    lt.SubItems.Add(dr.GetValue(1).ToString());
                    lt.SubItems.Add(dr.GetValue(2).ToString());
                    lt.SubItems.Add(dr.GetValue(3).ToString());
                    lt.SubItems.Add(dr.GetValue(4).ToString());
                    lt.SubItems.Add(dr.GetValue(5).ToString());
                    lv.Items.Add(lt);
                }
                dr.Close();

            }// end blokc
        }
       #endregion
        #region //返回查找結果DR  public SqlDataAdapter getWard()   
       
        public SqlDataReader getWard()   
        {
            con = getcon.GetCon();
            string P_str_select = "select * from tb_warrantInfo";
            cmd = new SqlCommand(P_str_select,con);
            SqlDataReader dr = cmd.ExecuteReader();
            return dr;
        }// end block if 
        #endregion
        #region //查找憑證信息SqlDataReader GetWarrarFInd(Warrant war,)
        public SqlDataReader GetWarrarFInd(Warrant war)
        { 
                con=getcon.GetCon();
                cmd=new SqlCommand();
                string P_str_select = "select a.subName,b.subLendmoney a ,b.subGoodsMoney ,b.warrantDate ,b.warrantId,b.subRemark from tb_subjectInfo a,tb_warrantInfo b where b.warrantId='" + war.getP_int_warrantId() + "' and ((a.subId=b.subid)and b.subRemark is null) ";
                cmd.CommandText=P_str_select;
                cmd.Connection=con;
                SqlDataReader dr=cmd.ExecuteReader();
                return dr;
        }// end block 
        #endregion
        #region//用戶名查找時間 string GetWarrdDate(string war)
        public string GetWarrdDate(string war)
        {
            string P_date_select = null;
            string P_str_select = "select logdate from tb_logInfo where username='" + war + "'";
            con = getcon.GetCon();
            cmd = new SqlCommand();
            cmd.CommandText = P_str_select;
            cmd.Connection = con;
            SqlDataReader dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                P_date_select = dr[0].ToString();
            }// end block 
            return P_date_select;
        }// end block 
        #endregion
        #region//刪除憑證信息  public string GetWarrantDelete(int P_int_warrID)
        public string GetWarrantDelete(int P_int_warrID)
        {
            string P_str_result = null;
            try
            {
                con = getcon.GetCon();
                cmd = new SqlCommand();
                string P_str_delete = "delete from tb_warrantInfo where warrantId ='" + P_int_warrID + "'";
                cmd.CommandText = P_str_delete;
                cmd.Connection = con;
                int P_int_inst = cmd.ExecuteNonQuery();