using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;


/// <summary>
/// MemberOperate 的摘要說明
/// </summary>
public class MemberOperate
{
    public MemberOperate()
    {
        //
        // TODO: 在此處添加構造函數邏輯
        //
    }
    public static SqlConnection creatrCon()     //連接數據庫;
    {
        return new SqlConnection("server=.;database=中南勘測;uid=sa;pwd=sa;");
    }
    public static bool finduser(string userName)   //查找用戶名;
    {

        SqlConnection con = MemberOperate.creatrCon();
        con.Open();
        SqlCommand cmd = new SqlCommand("select count(*) from Member where userName='" + userName + "'", con);
        int count = Convert.ToInt32(cmd.ExecuteScalar());
        if (count > 0)
        {
            return true;
        }
        else
        {
            return false;
        }

    }
    public static bool findIndentity(string MID)   //查找用戶身份;
    {
        SqlConnection con = MemberOperate.creatrCon();
        con.Open();
        SqlCommand cmd = new SqlCommand("select count(*) from Member where MID='" + MID + "'", con);
        int count = Convert.ToInt32(cmd.ExecuteScalar());
        if (count > 0)
        {
            return true;
        }
        else
        {
            return false;
        }
    }
    public static DataTable selectAllData()      //構造綁定GridView函數;
    {
        SqlConnection con = MemberOperate.creatrCon();
        SqlDataAdapter sda = new SqlDataAdapter();
        sda.SelectCommand = new SqlCommand("select * from Member", con);
        DataSet ds = new DataSet();
        sda.Fill(ds, "Member");
        return ds.Tables["Member"];
    }
    public static bool insertOperate(Member p)    //插入用戶到數據庫;
    {
        try
        {

            SqlConnection con = MemberOperate.creatrCon();
            con.Open();
            SqlCommand cmd = new SqlCommand("insert into Member values(@userName,@userPwd,@Indentity,@Department,@ID,@MID)", con);
            SqlParameter para = new SqlParameter("@userName", SqlDbType.VarChar, 50);
            para.Value = p.userName;
            cmd.Parameters.Add(para);
            para = new SqlParameter("@userPwd", SqlDbType.VarChar, 50);
            para.Value = p.userPwd;
            cmd.Parameters.Add(para);
            para = new SqlParameter("@Indentity", SqlDbType.VarChar, 50);
            para.Value = p.Indentity;
            cmd.Parameters.Add(para);
            para = new SqlParameter("@Department", SqlDbType.VarChar, 50);
            para.Value = p.Department;
            cmd.Parameters.Add(para);
            para = new SqlParameter("@ID", SqlDbType.VarChar, 50);
            para.Value = p.ID;
            cmd.Parameters.Add(para);
            para = new SqlParameter("@MID", SqlDbType.VarChar, 50);
            para.Value = p.MID;
            cmd.Parameters.Add(para);
            cmd.ExecuteNonQuery();
            return true;
        }
        catch (Exception e)
        {
            return false;
        }
    }
    public static bool deleteOperate(string userName)       //刪除數據庫中的用戶名;
    {
        try
        {
            SqlConnection con = MemberOperate.creatrCon();
            con.Open();
            SqlCommand cmd = new SqlCommand("delete from Member where userName='" + userName + "'", con);
            cmd.ExecuteNonQuery();
            return true;
        }
        catch (Exception e)
        {
            return false;
        }
    }
    public static bool insertmulu(string table, string pid, string nid, string name)    //插入記錄到數據庫的設備目錄表;
    {
        try
        {

            SqlConnection con = MemberOperate.creatrCon();
            con.Open();
            SqlCommand cmd = new SqlCommand("insert into " + table + " values('" + pid + "','" + nid + "','" + name + "')", con);
            cmd.ExecuteNonQuery();
            con.Close();
            return true;
        }
        catch (Exception e)
        {
            return false;
        }
    }

    public static bool updatemulu(string table, string id, string name)    //更新記錄到數據庫的設備目錄表;
    {
        try
        {

            SqlConnection con = MemberOperate.creatrCon();
            con.Open();
            SqlCommand cmd = new SqlCommand("update " + table + " set Name='" + name + "' where ID=" + id, con);
            cmd.ExecuteNonQuery();
            con.Close();
            return true;
        }
        catch (Exception e)
        {
            return false;
        }
    }
    public static bool updateproduct(string id,string produce, string type, string k1, string k2)    //更新記錄到數據庫的產品表;
    {
        try
        {

            SqlConnection con = MemberOperate.creatrCon();
            con.Open();
            SqlCommand cmd = new SqlCommand("update product set chuchang1=chuchang*" + k1 + ", FOB=chuchang*" + k1 + ", CI=chuchang*" + k1 + "*"+k2+" where ID='" + id + "' and Produce='" + produce + "' and Type='" + type + "'", con);
            cmd.ExecuteNonQuery();
            con.Close();
            return true;
        }
        catch (Exception e)
        {
            return false;
        }
    }
    public static bool updateproject(string pname,string sysname,string product, string produce, string type, string k1, string k2,string n)    //更新記錄到數據庫的項目表;
    {
        try
        {
            SqlConnection con = DB.createConnection();
            con.Open();
            SqlCommand cmd = new SqlCommand("update project set chuchang1=chuchang*" + k1 + ", FOB=chuchang*" + k1 + ", CI=chuchang*" + k1 + "*" + k2 + ", Num=" + n + " where Pname='" + pname + "' and Sysname='" + sysname + "' and Product='" + product + "' and Produce='" + produce + "'and Type='" + type + "'", con);
            cmd.ExecuteNonQuery();
            con.Close();
            return true;
        }
        catch (Exception e)
        {
            return false;
        }
    }
    public static bool intoproject(string pro, string sysname, string num,string id, string produce, string type)  //插入記錄到項目表
    {
        try
        {
            SqlConnection con = DB.createConnection();
            con.Open();
            SqlCommand cmd = new SqlCommand("insert into project select '"+pro+"','"+sysname+"',Product,Produce,Type,"+num+",UFOB,FIP,ULP,UEXW,CEXW,VAT,CIF,Parament,Remark,chuchang,chuchang1,FOB,CI from product where ID='" +id+ "' and Produce='" + produce + "'and Type='"+type+"'", con);
            cmd.ExecuteNonQuery();
            con.Close();
            return true;
        }
        catch (Exception e)
        {
            return false;
        }
    }
    public static bool intoproject0(string pro, string num, string pname, string sysname, string product, string produce, string type)  //從項目表中選定記錄插入到新項目
    {
        try
        {
            SqlConnection con = DB.createConnection();
            con.Open();
            SqlCommand cmd = new SqlCommand("insert into project select '" + pro + "',Sysname,Product,Produce,Type," + num + ",UFOB,FIP,ULP,UEXW,CEXW,VAT,CIF,Parament,Remark,chuchang,chuchang1,FOB,CI from project where Pname='" + pname + "' and Sysname='" + sysname + "' and Product='" + product + "' and Produce='" + produce + "'and Type='" + type + "'", con);
            cmd.ExecuteNonQuery();
            con.Close();
            return true;
        }
        catch (Exception e)
        {
            return false;
        }
    }
    public static bool newproject(string pro)       //新建一個項目
    {
        try
        {
            SqlConnection con = DB.createConnection();
            con.Open();
            SqlCommand cmd = new SqlCommand("insert into project(Pname,Sysname,Product,Produce,Type) values('"+pro+"','1','1','1','1')" , con);
            cmd.ExecuteNonQuery();
            con.Close();
            return true;
        }
        catch (Exception e)
        {
            return false;
        }
    }
    public static  bool coefficient()
    {
        SqlConnection con = MemberOperate.creatrCon();
        con.Open();
        //SqlCommand cmd = new SqlCommand("update " + table + " set Name='" + name + "' where ID=" + id, con);
        SqlCommand cmd = new SqlCommand("select number from coefficient where name='k'", con);
        decimal K = Convert.ToDecimal(cmd.ExecuteScalar());
        System.Web.HttpContext.Current.Application["K"] = K;

        SqlCommand cmd1 = new SqlCommand("select number from coefficient where name='k1'", con);
        decimal K1 = Convert.ToDecimal(cmd.ExecuteScalar());
        System.Web.HttpContext.Current.Application["K1"] = K1;

        SqlCommand cmd2 = new SqlCommand("select number from coefficient where name='k2'", con);
        decimal K2 = Convert.ToDecimal(cmd.ExecuteScalar());
        System.Web.HttpContext.Current.Application["K2"] = K2;
        con.Close();
        return true;
    }
    public static bool alterpara(string k,string k1,string k2)    //修改計算系數//
    {
        try
        {
            SqlConnection con = MemberOperate.creatrCon();
            con.Open();
            SqlCommand cmd = new SqlCommand("Alter table product drop column chuchang1", con);
            cmd.ExecuteNonQuery();
            SqlCommand cmda = new SqlCommand("alter table product add chuchang1  as chuchang*("+k+")", con);
            cmda.ExecuteNonQuery();

            SqlCommand cmd1 = new SqlCommand("Alter table product drop column FOB", con);
            cmd1.ExecuteNonQuery();
            SqlCommand cmd1a = new SqlCommand("alter table product add FOB as  chuchang*(" + k+ ")*("+k1+")", con);
            cmd1a.ExecuteNonQuery();

            SqlCommand cmd2 = new SqlCommand("Alter table product drop column CI", con);
            cmd2.ExecuteNonQuery();
            SqlCommand cmd2a = new SqlCommand("alter table product add CI as  chuchang*(" + k + ")*("+k1+")*("+k2+")", con);
            cmd2a.ExecuteNonQuery();
            con.Close();
            return true;
        }
        catch (Exception e)
        {
            return false;
        } 
    }
    public static bool insertProductmessageOperate(Member p)    //插入產品信息到數據庫;
    {
        try
        {

            SqlConnection con = MemberOperate.creatrCon();
            con.Open();
            SqlCommand cmd = new SqlCommand("insert into productmessage values(@ID,@制造商,@設備型號,@單個離岸價,@海外運輸及保險價格,@港卸貨價格,@單個出廠價,@運輸價,@增值稅,@公司所得稅,@參數,@備注)", con);
            SqlParameter para = new SqlParameter("@ID", SqlDbType.VarChar, 50);
            para.Value = p.ID;
            cmd.Parameters.Add(para);
            para = new SqlParameter("@制造商", SqlDbType.VarChar, 50);
            para.Value = p.制造商;
            cmd.Parameters.Add(para);
            para = new SqlParameter("@設備型號", SqlDbType.VarChar, 50);
            para.Value = p.設備型號;
            cmd.Parameters.Add(para);
            para = new SqlParameter("@單個離岸價", SqlDbType.VarChar, 50);
            para.Value = p.單個離岸價;
            cmd.Parameters.Add(para);
            para = new SqlParameter("@海外運輸及保險價格", SqlDbType.VarChar, 50);
            para.Value = p.海外運輸及保險價格;
            cmd.Parameters.Add(para);
            para = new SqlParameter("@港卸貨價格", SqlDbType.VarChar, 50);
            para.Value = p.港卸貨價格;
            cmd.Parameters.Add(para);
            para = new SqlParameter("@單個出廠價", SqlDbType.VarChar, 50);
            para.Value = p.海外運輸及保險價格;
            cmd.Parameters.Add(para);
            para = new SqlParameter("@運輸價", SqlDbType.VarChar, 50);
            para.Value = p.運輸價;
            cmd.Parameters.Add(para);
            para = new SqlParameter("@增值稅", SqlDbType.VarChar, 50);
            para.Value = p.增值稅;
            cmd.Parameters.Add(para);
            para = new SqlParameter("@公司所得稅", SqlDbType.VarChar, 50);
            para.Value = p.公司所得稅;
            cmd.Parameters.Add(para);
            para = new SqlParameter("@參數", SqlDbType.VarChar, 50);
            para.Value = p.參數;
            cmd.Parameters.Add(para);
            para = new SqlParameter("@備注", SqlDbType.VarChar, 50);
            para.Value = p.備注;
            cmd.Parameters.Add(para);
            cmd.ExecuteNonQuery();
            return true;
        }
        catch (Exception e)
        {
            return false;
        }
    }
    public static bool intopmessage(string username,string producer,string adress,string tel,string officetel,string fax,string email,string beizhu)    //插入廠家信息到數據庫;
    {
        try
        {
            SqlConnection con = MemberOperate.creatrCon();
            con.Open();            
            SqlCommand cmd = new SqlCommand("insert into Pmessage values('" + username + "','" + producer + "','" + adress + "','"+tel+"','" +officetel+"','"+fax+"','"+email+"','"+beizhu+"')", con);           
            cmd.ExecuteNonQuery();
            return true;
        }
        catch (Exception e)
        {
            return false;
        }
    }
}