using System;
using System.Collections;
using System.ComponentModel;
using System.Data.OleDb;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.IO;
using System.Data.SqlClient;
using System.Threading;
using System.Configuration;
using System.Reflection;



namespace WebApplication2.Admin
{
	/// <summary>
	/// AdvFinanceLog 的摘要说明。
	/// </summary>
	public partial class AdvPayMoney : System.Web.UI.Page 
	{
		private static string cmdstr;
		public string cmd_str
		{
			get
			{
				return cmdstr.ToString();
			}
			set
			{
				cmdstr=value;
			}
		}
        private static string Filepath="";
        private static string name = "";

		protected void Page_Load(object sender, System.EventArgs e)
		{
			if(!Page.IsPostBack)
			{
				if(this.Session["USERNAME"]==null||this.Session["Role"]==null||this.Session["USERNAME"].ToString()==""||this.Session["Role"].ToString()=="")
				{
					Response.Redirect("login.aspx");
				}
				else
				{
					string [] role=this.Session["Role"].ToString().Split('#');
					bool exists=false;
					for(int i=0;i<role.Length;i++)
					{
						if(role[i].Equals("7")||role[i].Equals("0"))
						{
							exists=true;break;
						}
					}
					if(exists)
					{
                        this.Session["ok"] = "True";
 						this.Label3.Visible=false;
						
					}
					else
					{Response.Redirect("NoAuthor.aspx");}
				}
			}
		}
		private void Bind(int record)
		{
			int maxrecord=20;
			int recordcount;
			int page;
			int startrecord=(record-1)*maxrecord;
			string connstr=System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
			SqlConnection conn=new SqlConnection (connstr);
			SqlDataAdapter da=new SqlDataAdapter("select * from WebsiteHost"+cmd_str+" order by id desc",conn);
			DataSet dataset=new DataSet();
			da.Fill(dataset,startrecord,maxrecord,"websit");
			this.DataGrid1.DataSource=dataset;
			this.DataGrid1.DataBind();
			conn.Close();
			SqlCommand cmd=new SqlCommand("select count(*) from WebsiteHost"+cmd_str,conn);
			conn.Open();
			recordcount=(int)cmd.ExecuteScalar();
			page=((recordcount%maxrecord)==0)?(recordcount/maxrecord):(recordcount/maxrecord+1);
				this.Label2.Text=page.ToString();
			if(this.DataGrid1.Items.Count>0)
			{
				this.Label1.Text=record.ToString();
				this.Label4.Text=recordcount.ToString();
				this.DropDownList1.Items.Clear();
				for(int i=1;i<=int.Parse(this.Label2.Text);i++)
				{
					this.DropDownList1.Items.Add(i.ToString());
				}
				this.DropDownList1.SelectedIndex=int.Parse(this.Label1.Text)-1;
			}
			else
			{
				this.Label1.Text="0";
				this.Label4.Text="0";
				this.DropDownList1.Items.Clear();
			}
			this.LinkButton1.Enabled=record<=1?false:true;
			this.LinkButton2.Enabled=record<=1?false:true;
			this.LinkButton3.Enabled=record<page?true:false;
			this.LinkButton4.Enabled=record<page?true:false;

					
		}

		protected void gopage(object sender, System.EventArgs e)
		{
			int prepage=Convert.ToInt32(this.Label1.Text.ToString());
		
			int nextpage=Convert.ToInt32(this.Label2.Text.ToString());
			string cmdname=((LinkButton)sender).CommandArgument.ToString();
			switch(cmdname)
			{
				case "pre":
					if(prepage>1)
						prepage--;break;
				case "next":
					if(prepage<nextpage)
						prepage++;break;
				case "frist":prepage=1;break;
				case "end":prepage=nextpage;break;
			}
			
			Bind(prepage);
		}
		
			
　
	


		// 在此处放置用户代码以初始化页面
	

		#region Web 窗体设计器生成的代码
		override protected void OnInit(EventArgs e)
		{
			//
			// CODEGEN: 该调用是 ASP.NET Web 窗体设计器所必需的。
			//
			InitializeComponent();
			base.OnInit(e);
		}
		
		/// <summary>
		/// 设计器支持所需的方法 - 不要使用代码编辑器修改
		/// 此方法的内容。
		/// </summary>
		private void InitializeComponent()
		{    

		}
		#endregion
        private void createfile()
        {
            name = DateTime.Now.Year.ToString() + "_" + DateTime.Now.Month.ToString() + "_" + DateTime.Now.Day.ToString() + "_" + DateTime.Now.Hour.ToString() + "_" + DateTime.Now.Minute.ToString() + "_" + DateTime.Now.Second.ToString() + ".xls";
            DirectoryInfo di = new DirectoryInfo(Server.MapPath(@"datasave"));
            if (!di.Exists)
            {
                di.Create();
            }
            FileInfo file = new FileInfo(di + "\\mode.xls");
            if (file.Exists)
            {
                file.CopyTo(di + "\\" + name, true);
                Filepath = di + "\\" + name;
            }
            else
            {
                try
                {
                    Excel.Application xApp = new Excel.Application();
                    Excel._Workbook book = xApp.Application.Workbooks.Add(true);
                    Excel.Worksheet xSheet = (Excel.Worksheet)book.Sheets[1];
                    xApp.Cells[1, 1] = "用户账号";
                    xApp.Cells[1, 2] = "姓名";
                    xApp.Cells[1, 3] = "银行";
                    xApp.Cells[1, 4] = "银行账号";
                    xApp.Cells[1, 5] = "电话";
                    xApp.Cells[1, 6] = "应付金额";
                    xApp.Cells[1, 7] = "实付金额";
                    xApp.Cells[1, 8] = "余额";
                    xApp.Cells[1, 9] = "状态";
                    xApp.Cells[1, 10] = "流水号";
                    book.SaveAs(file.FullName.ToString(), Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
                    xApp.Quit();
                    file.CopyTo(di + "\\" + name, true);
                    Filepath = di + "\\" + name;
                }
                catch
                {
                    Response.Write("<script>alert('模板文件出错，请与管理员联系！')</script>");
                }

            }

        }

	    private void savedata()
		{
           
             

		}

		protected void Button1_Click(object sender, System.EventArgs e)
		{
          
			string  money1=this.Textbox2.Text.Trim(),money2=this.Textbox3.Text.Trim();
			string name=SB.ADOHelper.inputText(this.TextBox1.Text.Trim());
			if(name=="")
			{
				name=" is not null";
			}
			else
			{
				name="='"+name+"'";
			}

			if(money1=="")
			{
		
				if(money2=="")
				{
					cmd_str=" where UserName"+name+" and Money>0"; 
				}
				else { cmd_str=" where UserName"+name+" and Money>0 and money<="+Convert.ToDouble(money2) ;}
			}
			else
			{
		
				if(money2=="")
				{
					cmd_str=" where UserName "+name+" and Money>0 and money>="+Convert.ToDouble(money1);
				}
				else
				{
					cmd_str=" where UserName "+name+" and Money>0 and Money>="+Convert.ToDouble(money1)+" and money<="+Convert.ToDouble(money2);
				}

			}
			Bind(1);
			if(this.DataGrid1.Items.Count>0)
            { this.Label3.Visible = false; Button3.Enabled = true; }
			else
            { this.Label3.Visible = true; Button3.Enabled = false;}
			
		}

		protected void Button3_Click(object sender, System.EventArgs e)
		{
                createfile();
                string id = "";
                double sum = 0.0;
                double totalpay = 0.0;
                double proceduremoney = 0.0;
                double textmoney = 0.0;
                double paymoney = 0.0;
                string advlistid, websiteid;
                SB.ADOHelper help = new SB.ADOHelper();
                SqlConnection sqlconn = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"].ToString());
                sqlconn.Open();
                SqlCommand cmd1 = new SqlCommand();
                SqlCommand cmd2 = new SqlCommand();
                 if (this.DataGrid1.Items.Count > 0)
                 {
                     for (int i = 0; i < this.DataGrid1.Items.Count; i++)
                     {
                         if (((CheckBox)DataGrid1.Items[i].FindControl("chk")).Checked)
                         {
                               
                               cmd1.Connection = sqlconn;
                               cmd1.CommandText = "select * from WebsiteHost where UserName='" + DataGrid1.Items[i].Cells[0].Text + "' and  Money>0 and money" + (Textbox2.Text.Trim() != "" ? ">="+Textbox2.Text : " is not null")+" and  money" + (Textbox2.Text.Trim() != "" ? "<=" +Textbox3.Text : " is not null");
                               SqlDataReader rd = cmd1.ExecuteReader();
                               if(rd.Read())
                               {
                                  
                                   totalpay = Convert.ToDouble(this.DataGrid1.Items[i].Cells[5].Text);
                                   proceduremoney = Convert.ToDouble(this.DataGrid1.Items[i].Cells[5].Text) * 0.05;
                                   textmoney = Convert.ToDouble(this.DataGrid1.Items[i].Cells[5].Text) * 0.01;
                                   paymoney = Convert.ToDouble(((DataBoundLiteralControl)this.DataGrid1.Items[i].Cells[6].Controls[0]).Text);
                                   id = this.DataGrid1.DataKeys[this.DataGrid1.Items[i].ItemIndex].ToString();
                                   if (help.conn.State == ConnectionState.Closed)
                                   { help.conn.Open(); }
                                   cmd2.Connection = help.conn;
                                   string cmdtext = "update WebsiteHost set Money=Money-" + this.DataGrid1.Items[i].Cells[5].Text + " where id='" + id + "'";
                                   cmd2.CommandText = cmdtext;
                                   SqlTransaction tran = help.conn.BeginTransaction();
                                   cmd2.Transaction = tran;
                                   try
                                   {
                                       cmd2.ExecuteNonQuery();
                                       cmd2.CommandText = "insert into FinanceWeb(AffairsType,WebUserName,AdvListAdvTitle,ClosebookMoney,AdvPeriod,RewardMoney,PayTotalMoney,ProcedureMoney,TaxMoney,PayMoney,Remarks,Surplusamount,AddTime) values('2','" + this.DataGrid1.Items[i].Cells[0].Text + "','',0.0,'" + DateTime.Now.ToShortDateString() + "',0.0," + totalpay + "," + proceduremoney + "," + textmoney + "," + paymoney + ",'付款',0.0,'" + DateTime.Now.ToShortDateString() + "')";
                                       cmd2.ExecuteNonQuery();
                                       sum += paymoney;
                                       tran.Commit();
                                   }
                                   catch
                                   {
                                       tran.Rollback();
                                       SB.ADOHelper.alertmsg("数据发生错误未继续!", "AdvFinance.aspx"); break;
                                   }
                                   string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Filepath + ";" + "Extended Properties=Excel 8.0;";
                                   OleDbConnection conn = new OleDbConnection(strConn);
                                   conn.Open();
                                   System.Data.OleDb.OleDbCommand cmd = new OleDbCommand();
                                   try
                                   {           
                                               cmd.CommandText = "insert into [sheet1$](用户账号,姓名,银行,银行账号,电话,应付金额,实付金额,余额,状态,流水号)VALUES('" + DataGrid1.Items[i].Cells[0].Text + "','" + DataGrid1.Items[i].Cells[1].Text + "','" + DataGrid1.Items[i].Cells[2].Text + "','" + DataGrid1.Items[i].Cells[3].Text + "','" + DataGrid1.Items[i].Cells[4].Text + "','" + DataGrid1.Items[i].Cells[5].Text + "','" + ((DataBoundLiteralControl)DataGrid1.Items[i].Cells[6].Controls[0]).Text.ToString() + "','" + ((DataBoundLiteralControl)DataGrid1.Items[i].Cells[7].Controls[0]).Text.ToString() + "','" + ((DataBoundLiteralControl)DataGrid1.Items[i].Cells[8].Controls[0]).Text.ToString() + "','" + DataGrid1.Items[i].Cells[9].Text + "')";
                                               cmd.Connection = conn;
                                               cmd.ExecuteNonQuery();               
                                   }
                                   catch
                                   {
                                       Response.Write("<script>alert('保存数据出错！')</script>"); break;
                                   }
                                   conn.Close();           	
                               }
                               rd.Close();
                            
                         }
                         
                         
                     }
                     if (sum > 0)
                     {
                         cmd2.CommandText = "insert into paymoneylog(SystemUser,Event,Money,FileName,AddTime) values('" + Session["USERNAME"].ToString() + "','2'," + sum + ",'" + name + "','" + DateTime.Now.ToString() + "')";
                         cmd2.ExecuteNonQuery();
                         Bind(1);
                         Response.Write("<script>alert('支付完成！')</script>");
                     }
                     else
                     {
                         FileInfo file = new FileInfo(Filepath);
                         if (file.Exists)
                         {
                             file.Delete();
                         }
                         Response.Write("<script>alert('已全部被结算!')</script>");
                     }
                     
                     if (this.DataGrid1.Items.Count > 0)
                     { this.Label3.Visible = false; Button3.Enabled = true; }
                     else
                     { this.Label3.Visible = true; Button3.Enabled = false; }
                 }
                 sqlconn.Close();
                 help.conn.Close();

          
		}

		protected void DropDownList1_SelectedIndexChanged(object sender, System.EventArgs e)
		{
			Bind(int.Parse(this.DropDownList1.SelectedItem.Text));
		}

        protected void Button4_Click(object sender, EventArgs e)
        {
            for (int i = 0; i < DataGrid1.Items.Count; i++)
            {
                ((CheckBox)DataGrid1.Items[i].FindControl("chk")).Checked = true;
            }
        }
        protected void Button2_Click(object sender, EventArgs e)
        {
            for (int i = 0; i < DataGrid1.Items.Count; i++)
            {
                if (((CheckBox)DataGrid1.Items[i].FindControl("chk")).Checked)
                {
                    ((CheckBox)DataGrid1.Items[i].FindControl("chk")).Checked = false;
                }
                else
                {
                    ((CheckBox)DataGrid1.Items[i].FindControl("chk")).Checked = true;
                }
            }
        }
        protected void Button5_Click(object sender, EventArgs e)
        {
            for (int i = 0; i < DataGrid1.Items.Count; i++)
            {
                ((CheckBox)DataGrid1.Items[i].FindControl("chk")).Checked =false;
            }
        }
}

	}
