using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.OleDb;//引入oledb
using System.Data;
using System.Collections;
using System.Windows.Forms;
using System.IO;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Text;
/// <summary>
/// 描述：数据访问类，业务处理类
/// 功能：提供数据访问，业务处理的方法
/// 作者：R3
/// 时间：2010-9-20 14:47:28
/// </summary>
public class SaleInfoManager
{
HttpResponse Response = null;
public SaleInfoManager(HttpResponse response)
{
Response = response;
//
// TODO: Add constructor logic here
//
}
//数据库连接
private static OleDbConnection con;
public static OleDbConnection Con
{
get
{
if (con == null)
{
con = new OleDbConnection
(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\db1.mdb;"+
"Persist Security Info=True");
}
else if (con.State == ConnectionState.Broken)
{
con.Close();
con.Open();
}
else if (con.State == ConnectionState.Closed )
{
con.Open();
}
return con;
}
}
//导出文件
public void Export(string FileName, GridView gridview)
{
Response.Charset = "GB2312";
Response.ContentEncoding = System.Text.Encoding.UTF7;
Response.AppendHeader("Content-Disposition", "attachment;filename="
+ HttpUtility.UrlEncode(FileName, Encoding.UTF8).ToString());
Response.ContentType = "application/ms-excel";//导出excel文件
//this.EnableViewState = false;
StringWriter tw = new StringWriter();
HtmlTextWriter hw = new HtmlTextWriter(tw);
gridview.RenderControl(hw);
Response.Write(tw.ToString());
Response.End();
}
#region GetAllKnowledge 得到所有的知识库
public DataTable GetAllKnowledge()
{
string sql = "select * from XWE_xknowledge";
DataTable dt = GetBySQL(sql);
return dt;
}
#endregion
#region UpdateKnowledge 修改知识库
public static int UpdateKnowledge(int id,string title,string content)
{
string sql = string.Format("update XWE_Xknowledge set x_title='{0}',"
+" x_content='{1}' where x_knowid={2} ",title,content,id);
int rs = ExcuteCmd(sql);
return rs;
}
#endregion
#region CheckDBExistsIp 检查IP是否在数据库中存在
public static bool CheckDBExistsIp(string ip,int id)
{
string checkTime = DateTime.Today.ToString("yyyy/MM/dd");
string sql = string.Format("select top 1 * from xwe_ip "
+" where x_id={0} and x_ip='{1}' and x_clickTime=#{2}# ",id, ip, checkTime);
DataTable dt = GetBySQL(sql);
if (dt.Rows.Count==0)
{
return false;
}
else
{
return true;
}
}
#endregion
#region CheckDBExistsIp 检查IP是否在数据库中存在
public static bool CheckDBExistsFlow(string ip)
{
string checkTime = DateTime.Today.ToString("yyyy/MM/dd");
string sql = string.Format("select top 1 * from xwe_Flow "
+" where x_Fip='{0}' and x_Ftime=#{1}# ", ip, checkTime);
DataTable dt = GetBySQL(sql);
if (dt.Rows.Count == 0)
{
return false;
}
else
{
return true;
}
}
#endregion
#region CheckIpExists 检查IP是否存在,是否点击超过2次
public static bool CheckIp(string ip, HttpApplicationState app)
{
int maxIpNum = 1;//设置最大一天可以访问5次
Hashtable hsIp;
//当application不存在，或者日期不对的时候，重新设定
if (app["IpHashTable"] == null || app["IpHashTableDate"]
== null || app["IphashTableDate"].ToString()
!= DateTime.Now.ToString("yyyyMMdd"))
{
app.Lock(); //锁定，排除
app["IpHashTable"] = new Hashtable(); //初始化IP和次数
app["IpHashTableDate"] = DateTime.Now.ToString("yyyyMMdd");//初始化日期
app.UnLock();//解锁
}
hsIp = (Hashtable)app["IpHashTable"];
if (hsIp[ip] == null)//当Ip在application中不存在
{
hsIp[ip] = 1;
return true;
}
//如果没到5次
hsIp[ip] = Convert.ToInt32(hsIp[ip]) + 1;
if (Convert.ToInt32(hsIp[ip]) > maxIpNum)
{
return false;
}
else
{
return true;
}
}
#endregion
#region GetBySQL 查询-返回datatable
public static DataTable GetBySQL(string sql)
{
using (OleDbCommand cmd = new OleDbCommand(sql, Con))
{//执行命令
using (OleDbDataAdapter da = new OleDbDataAdapter(cmd))
{//装载结果
using (DataTable dt = new DataTable())//创建datata
{
da.Fill(dt);//填充datatable
return dt;//返回结果
}
}
}
}
#endregion
#region
/// <summary>
/// 后台小类分页
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static IList<_P_Category> FillSmallPager(int pagesize,int start)
{
string s_p=string.Empty;
if (start == 0)
{
s_p = string.Format
("select c_id,c_name from XWE_P_Category ");
}
else
{
s_p = string.Format
("select top {0} c_id,c_name from XWE_P_Category where (c_id not in (select top {1} c_id from XWE_P_Category))"
, pagesize, start);
}
IList<_P_Category> list = new List<_P_Category>();
_P_Category _p_c = null;
DataTable dt = SaleInfoManager.GetBySQL(s_p);
foreach (DataRow dr in dt.Rows)
{
_p_c = new _P_Category();
_p_c.C_id = (int)dr["c_id"];
_p_c.C_name = (string)dr["c_name"];
list.Add(_p_c);
}
return list;
}
#endregion
#region
/// <summary>
/// 后台产品大类分页
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static IList<Bigtype> FillBigPager(int pagesize, int start)
{
string s_p = string.Empty;
if (start == 0)
{
s_p = string.Format
("select * from XWE_bigtype ");
}
else
{
s_p = string.Format
("select top {0} B_id,B_name,b_cid from XWE_bigtype where (B_id not in (select top {1} b_id from XWE_bigtype where b_cid=1))"
, pagesize, start);
}
IList<Bigtype> list = new List<Bigtype>();
Bigtype _p_c = null;
DataTable dt = SaleInfoManager.GetBySQL(s_p);
foreach (DataRow dr in dt.Rows)
{
_p_c = new Bigtype();
_p_c.B_id = (int)dr["b_id"];
_p_c.B_name = (string)dr["b_name"];
_p_c.B_cid = (int)dr["b_cid"];
list.Add(_p_c);
}
return list;
}
#endregion
#region
/// <summary>
/// 删除方法
/// </summary>
/// <param name="id"></param>
/// <param name="num"></param>
/// <returns></returns>
public static bool DeleteById(int id, int num)
{
string s_d = string.Empty;
switch (num)
{
case 1:
s_d=string.Format (
"delete from XWE_bigtype where (b_id={0})",id
);
break;
case 2:
s_d = string.Format(
"delete from XWE_P_Category where (c_id={0})", id
);
break;
default:
break;
}
int rs=SaleInfoManager.ExcuteCmd(s_d);
return rs == 1 ? true : false;
}
#endregion
#region
/// <summary>
/// 更新产品小类
/// </summary>
/// <param name="id"></param>
/// <param name="name"></param>
/// <returns></returns>
public static bool UpdateSmallType(int id,string name,int num,int b_id)
{
string s_u =string.Empty;
switch (num)
{
case 1:
s_u = string.Format("update XWE_P_Category set c_name='{0}' where c_id={1}",name,id);
break;
case 2:
s_u = string.Format("update XWE_bigtype set b_name='{0}',b_cid={1} where b_id={2}", name,b_id,id);
break;
default:
break;
}
int Result = SaleInfoManager.ExcuteCmd(s_u);
return Result ==1 ? true : false;
}
#endregion
#region ExcuteCmd 执行修改，删除,添加
public static int ExcuteCmd(string sql)
{
OleDbCommand cmd = new OleDbCommand(sql, Con);//执行命令
int rs = cmd.ExecuteNonQuery();//返回执行结果
//con.Close();
return rs;
}
#endregion
#region LoginByUserName 销售员登录
public static DataTable LoginByUserName(string x_name)
{
string loginSql = string.Format
("select * from XWE_Xsbm where x_name='{0}'", x_name);
DataTable dt = GetBySQL(loginSql);
return dt;
}
#endregion
#region UpdateSaleInfo 修改销售员信息
public static int UpdateSaleInfo(string sql)
{
int rs = ExcuteCmd(sql);
return rs;
}
#endregion
#region GetAllSaleInfo 查询所有销售员信息
public static DataTable GetAllSaleInfo()
{
string sql = "select * from XWE_Xsbm order by x_auctionMoney desc";
return GetBySQL(sql);
}
#endregion
#region UpdatePwd 修改密码
public static int UpdatePwd(string pwd, string userName)
{
string sql = string.Format("update XWE_Xsbm set x_pwd='{0}'"
+ " where x_name='{1}'", pwd, userName);
return ExcuteCmd(sql);
}
#endregion
#region GetSaleInfoByUserName 根据用户名，密码查询
public static DataTable GetSaleInfoByUserName(string userName, string Pwd)
{
string sql = string.Format("select * from XWE_Xsbm where x_name='{0}'"
+" and x_pwd='{1}'", userName, Pwd);
return GetBySQL(sql);
}
#endregion
#region IsNumber 判断是否是数字
public static bool IsNuber(string number)
{
bool isCheck = true;
if (string.IsNullOrEmpty(number))
{
isCheck = false;
}
else
{
char[] charNumber = number.ToCharArray();
for (int i = 0; i < charNumber.Length; i++)
{
if (!char.IsNumber(charNumber[i]))
{
isCheck = false;
break;
}
}
}
return isCheck;
}
#endregion
#region GetIp 获得IP地址
public static string GetIp()
{
if (System.Web.HttpContext.Current.Request.ServerVariables["HTTP_VIA"] != null)
{
return System.Web.HttpContext.Current.Request.ServerVariables["HTTP_X_FORWARDED_FOR"].
Split(new char[] { ',' })[0];
}
else
{
return System.Web.HttpContext.Current.Request.ServerVariables["REMOTE_ADDR"];
}
}
#endregion
#region ExportDataGridViewToExcel 导出到Excel
public static void ExportDataGridViewToExcel(DataGridView dataGridview1)
{
SaveFileDialog saveFileDialog = new SaveFileDialog();
saveFileDialog.Filter = "Execl files (*.xls)|*.xls";
saveFileDialog.FilterIndex = 0;
saveFileDialog.RestoreDirectory = true;
saveFileDialog.CreatePrompt = true;
saveFileDialog.Title = "导出Excel文件到";
DateTime now = DateTime.Now;
saveFileDialog.FileName = now.Year.ToString().PadLeft(2)
+ now.Month.ToString().PadLeft(2, '0') + now.Day.ToString().PadLeft(2, '0')
+ "-" + now.Hour.ToString().PadLeft(2, '0') + now.Minute.ToString().PadLeft(2, '0')
+ now.Second.ToString().PadLeft(2, '0');
DialogResult dr = saveFileDialog.ShowDialog();
//如果选确定 执行 否则不执行了解
if (dr == DialogResult.OK)
{
Stream myStream;
myStream = saveFileDialog.OpenFile();
StreamWriter sw = new StreamWriter
(myStream, System.Text.Encoding.GetEncoding("gb2312"));
string str = "";
try
{
//写标题
for (int i = 0; i < dataGridview1.ColumnCount; i++)
{
if (i > 0)
{
str += "\t";
}
str += dataGridview1.Columns[i].HeaderText;
}
sw.WriteLine(str);
//写内容
for (int j = 0; j < dataGridview1.Rows.Count; j++)
{
string tempStr = "";
for (int k = 0; k < dataGridview1.Rows[j].Cells.Count; k++)
{
if (k > 0)
{
tempStr += "\t";
}
if (dataGridview1.Rows[j].Cells[k].Value != null)
{
tempStr += dataGridview1.Rows[j].Cells[k].Value.ToString();
}
}
sw.WriteLine(tempStr);
}
sw.Close();
myStream.Close();
}
catch (Exception e)
{
throw e;
}
finally
{
sw.Close();
myStream.Close();
}
}
}
#endregion
}