private void GetDepositor(string AccountNumber)
{
depositorInfo.Style.Add("display", "none");
divPreDefine.Style.Add("display", "none");
common.IsLogin();
string WBID = Session["WB_ID"].ToString();//当前网点ID
StringBuilder strSql = new StringBuilder();
strSql.Append(" select ID,WBID,AccountNumber,strPassword,ZuID,strAddress,strName,IDCard,PhoneNO,FieldCopies,FieldCount,ApplicationForm,CommunePic,dt_Commune,numState");
strSql.Append(" FROM dbo.Commune");
strSql.Append(" ");
strSql.Append(" ");
strSql.Append(" where 1=1 and WBID= " + WBID);
strSql.Append(" and AccountNumber=@AccountNumber ");
SqlParameter[] parameters =
{
new SqlParameter("@AccountNumber", SqlDbType.NVarChar, 50)
};
parameters[0].Value = AccountNumber;
DataTable dt = SQLHelper.ExecuteDataTable(strSql.ToString(), parameters);
if (dt != null && dt.Rows.Count != 0)
{
depositorInfo.Style.Add("display", "block");
D_strName.InnerText = dt.Rows[0]["strName"].ToString();
D_strAddress.InnerText = dt.Rows[0]["strAddress"].ToString();
D_PhoneNo.InnerText = dt.Rows[0]["PhoneNo"].ToString();
D_AccountNumber.InnerText = dt.Rows[0]["AccountNumber"].ToString();
D_FieldCopies.InnerText = dt.Rows[0]["FieldCopies"].ToString();
D_IDCard.InnerText = dt.Rows[0]["IDCard"].ToString();
//预存款信息
StringBuilder strSqlStorage = new StringBuilder();
strSqlStorage.Append(" SELECT A.ID,B.strRealName AS UserID,A.C_AccountNumber,A.Money_PreDefine,C.strName AS GoodSupplyID,CONVERT(varchar(100), A.dt_Trade, 23) AS dt_Trade,DATEDIFF(DAY,A.dt_Trade,GETDATE()) AS numday");
strSqlStorage.Append(" FROM dbo.C_PreDefine A INNER JOIN dbo.Users B ON A.UserID=B.ID");
strSqlStorage.Append(" INNER JOIN dbo.GoodSupply C ON A.GoodSupplyID=C.ID");
strSqlStorage.Append(" WHERE ISUsed=0 and C_AccountNumber='" + AccountNumber + "'");
DataTable dtStorage = SQLHelper.ExecuteDataTable(strSqlStorage.ToString());
if (dtStorage != null && dtStorage.Rows.Count != 0)
{
divPreDefine.Style.Add("display", "block");
RPreDefine.DataSource = dtStorage;
RPreDefine.DataBind();
}
}
else
{
Fun.Alert("您查询的社员不存在");
return;
}
}
protected void ImageButton1_Click(object sender, ImageClickEventArgs e)
{
if (Request.Form["QWBID"] == null || Request.Form["QWBID"].ToString() == "")
{
Fun.Alert("请选择网点！");
return;
}
GetRecord();
BindData(AspNetPager1.CurrentPageIndex - 1, AspNetPager1.PageSize);
}
protected void ImageButton1_Click(object sender, ImageClickEventArgs e)
{
object objWB = SQLHelper.ExecuteScalar(" SELECT COUNT(ID) FROM dbo.WB WHERE strName ='" + txtWBID.Value.Trim() + "'");
if (Convert.ToInt32(objWB) <= 0)
{
Fun.Alert("您查询的网点不存在");
return;
}
DataBind(txtWBID.Value);
}
private void DataBind(string WBName)
{
//获取存粮信息
StringBuilder strSql = new StringBuilder();
strSql.Append(" SELECT B.strName AS WBName,A.ID,A.SA_AN,V.strName AS Variety_Name,VarietyLevel_Name,Weight_Mao,Weight_Pi,Weight_Jing,Weight_Reality,CONVERT(VARCHAR(100),dt_Trade,23) AS dt_Trade");
strSql.Append(" FROM dbo.SA_CheckOut A INNER JOIN WB B ON A.WBID=B.ID");
strSql.Append(" INNER JOIN dbo.StorageVariety V ON A.Variety_Name=V.ID");
if (WBName.ToString().Trim() != "")
{
strSql.Append(" WHERE B.strName = '" + WBName + "'");
}
strSql.Append(" ORDER BY A.dt_Trade DESC");
DataTable dt = SQLHelper.ExecuteDataTable(strSql.ToString());
if (dt == null || dt.Rows.Count == 0)
{
Fun.Alert("没有查询到出库记录!");
Repeater1.Visible = false;
Repeater2.Visible = false;
return;
}
Repeater1.DataSource = dt;
Repeater1.DataBind();
Repeater1.Visible = true;
Repeater2.Visible = true;
StringBuilder strSqlTotal = new StringBuilder();
strSqlTotal.Append(" SELECT B.strName AS WBName,V.strName AS Variety_Name,");
strSqlTotal.Append(" SUM( Weight_Mao) AS Weight_Mao, SUM( Weight_Pi) AS Weight_Pi, SUM( Weight_Jing) AS Weight_Jing, SUM( Weight_Reality) AS Weight_Reality");
strSqlTotal.Append(" FROM dbo.SA_CheckOut A INNER JOIN WB B ON A.WBID=B.ID");
strSqlTotal.Append(" INNER JOIN dbo.StorageVariety V ON A.Variety_Name=V.ID");
if (WBName.ToString().Trim() != "")
{
strSqlTotal.Append(" WHERE B.strName = '" + WBName + "'");
}
strSqlTotal.Append(" GROUP BY B.strName,V.strName");
DataTable dtTotal = SQLHelper.ExecuteDataTable(strSqlTotal.ToString());
Repeater2.DataSource = dtTotal;
Repeater2.DataBind();
}
protected void btnStorageLevel_Click(object sender, EventArgs e)
{
if (Session["isCheck"] == null || Convert.ToBoolean(Session["isCheck"]) == false)
{
return;
}
StringBuilder sqlLevel=new StringBuilder ();
sqlLevel.Append(" SELECT L.VarietyID,L.VarietyLevelID,B.strName AS VarietyLevelName");
sqlLevel.Append(" FROM dbo.StorageVarietyLevel_L L INNER JOIN dbo.StorageVarietyLevel_B B ON L.VarietyLevelID=B.ID");
sqlLevel.Append(" ORDER BY B.ID");
DataTable dtLevel = SQLHelper.ExecuteDataTable(sqlLevel.ToString());
Dictionary<string, string> dicLevel = new Dictionary<string, string>();
for (int i = 0; i < dtLevel.Rows.Count; i++)
{
if (!dicLevel.Keys.Contains(dtLevel.Rows[i]["VarietyID"].ToString()))
{
dicLevel.Add(dtLevel.Rows[i]["VarietyID"].ToString(), dtLevel.Rows[i]["VarietyLevelID"].ToString());
}
}
StringBuilder sql = new StringBuilder();
foreach (KeyValuePair<string, string> kv in dicLevel)
{
string key = kv.Key;
string value = kv.Value;
sql.Append(string.Format( " UPDATE dbo.SA_VarietyStorage SET VarietyLevelID={0} WHERE VarietyID={1}",value,key));
sql.Append(string.Format(" UPDATE dbo.SA_VarietyStorageLog SET VarietyLevelID={0} WHERE VarietyID={1}", value, key));
sql.Append(string.Format(" UPDATE dbo.SA_CheckOut SET VarietyLevel_Name={0} WHERE Variety_Name={1}", value, key));
Console.WriteLine(kv.Key + kv.Value);
}
using (SqlTransaction tran = SQLHelper.BeginTransaction(SQLHelper.connectionString))
{
try
{
SQLHelper.ExecuteNonQuery(tran, CommandType.Text, sql.ToString());
tran.Commit();
Fun.Alert("初始化成功!");
}
catch
{
tran.Rollback();
Fun.Alert("初始化失败!");
}
}
}
private void outputExcel()
{
DataTable dt = GetoutputTable();
if (dt == null || dt.Rows.Count == 0)
{
Fun.Alert("请先检索要查询的信息后在导出！");
return;
}
string fileName = "业务统计" + DateTime.Now.Year + DateTime.Now.Month + DateTime.Now.Day + ".xls";
HttpResponse resp;
resp = Page.Response;
resp.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
resp.AppendHeader("Content-Disposition", "attachment;filename=" + fileName);
string colHeaders = "", ls_item = "", ls_Bottom = "";
DataRow[] myRow = dt.Select();//可以类似dt.Select("id>10")之形式达到数据筛选目的
int i = 0;
int cl = dt.Columns.Count;
colHeaders += "网点\t商品种类\t商品单价\t折率\t计量单位\t购买数量\t商品总价\t优惠金额\t预付款金额\t实付金额\n";
resp.Write(colHeaders);
//向HTTP输出流中写入取得的数据信息
//逐行处理数据
foreach (DataRow row in myRow)
{
//当前行数据写入HTTP输出流，并且置空ls_item以便下行数据
for (i = 0; i < cl; i++)
{
if (i == (cl - 1))//最后一列，加\n
{
ls_item += row[i].ToString() + "\n";
}
else
{
ls_item += row[i].ToString() + "\t";
}
}
resp.Write(ls_item);
ls_item = "";
}
resp.End();
}
protected void ImageButton1_Click(object sender, ImageClickEventArgs e)
{
StringBuilder strSql = new StringBuilder();
strSql.Append(" SELECT COUNT(A.ID)");
strSql.Append(" FROM dbo.SA_Account A INNER JOIN dbo.WB B ON A.WBID=B.ID");
strSql.Append(" WHERE B.strName ='" + txtWBID.Value.Trim() + "'");
object objWB = SQLHelper.ExecuteScalar(strSql.ToString());
if (Convert.ToInt32(objWB) <= 0)
{
Repeater1.Visible = false;
Repeater2.Visible = false;
Fun.Alert("您查询的网点不存在或该网点尚未出库开户");
return;
}
DataBind(txtWBID.Value);
}
protected void btnInitStorage_Click(object sender, EventArgs e)
{
if (Session["isCheck"] == null || Convert.ToBoolean(Session["isCheck"]) == false)
{
return;
}
DataTable dtLog = SQLHelper.ExecuteDataTable(" SELECT ID FROM dbo.StorageRate");
DataTable dtWB = SQLHelper.ExecuteDataTable(" select ID from WB");
if (dtLog == null || dtLog.Rows.Count == 0)
{
Fun.Alert("没有操作记录!");
return;
}
StringBuilder sql = new StringBuilder();
for (int i = 0; i < dtLog.Rows.Count; i++)
{
string rateID = dtLog.Rows[i]["ID"].ToString();
for (int j = 0; j < dtWB.Rows.Count; j++)
{
string wbid = dtWB.Rows[j]["ID"].ToString();
sql.Append(string.Format(" INSERT INTO StorageRate_WB(StorageRateID,WBID) VALUES({0},{1})", rateID, wbid));
}
}
using (SqlTransaction tran = SQLHelper.BeginTransaction(SQLHelper.connectionString))
{
try
{
SQLHelper.ExecuteNonQuery(tran, CommandType.Text, sql.ToString());
tran.Commit();
Fun.Alert("初始化成功!");
}
catch
{
tran.Rollback();
Fun.Alert("初始化失败!");
}
}
}
protected void ImageButton1_Click(object sender, ImageClickEventArgs e)
{
string WBID = Request.Form["QWBID"].ToString();
if (WBID == "" || WBID == "0")
{
Fun.Alert("请选择查询网点!");
return;
}
if (Qdtstart.Value.Trim() == "")
{
Fun.Alert("请输入开始查询日期");
return;
}
if (Qdtend.Value.Trim() == "")
{
Fun.Alert("请输入结束查询日期");
return;
}
BindData(WBID, Qdtstart.Value.Trim(), Qdtend.Value.Trim());
}
protected void btnInitStorage_Click(object sender, EventArgs e)
{
if (Session["isCheck"] == null || Convert.ToBoolean(Session["isCheck"]) == false)
{
return;
}
DataTable dtLog = SQLHelper.ExecuteDataTable(" SELECT * FROM dbo.Dep_OperateLog");
if (dtLog == null || dtLog.Rows.Count == 0)
{
Fun.Alert("没有操作记录!");
return;
}
StringBuilder sql = new StringBuilder();
for (int i = 0; i < dtLog.Rows.Count; i++)
{
string VarietyName = dtLog.Rows[i]["VarietyID"].ToString();
string UnitName = dtLog.Rows[i]["UnitID"].ToString();
string ID = dtLog.Rows[i]["ID"].ToString();
sql.Append(string.Format(" UPDATE dbo.Dep_OperateLog SET VarietyID=(SELECT ID FROM dbo.StorageVariety WHERE strName='{0}') WHERE dbo.Dep_OperateLog.ID={1}", VarietyName, ID));
//sql.Append(string.Format(" UPDATE dbo.Dep_OperateLog SET UnitID=(SELECT ID FROM dbo.BD_MeasuringUnit WHERE strName='{0}') WHERE dbo.Dep_OperateLog.ID={1}", UnitName, ID));
}
using (SqlTransaction tran = SQLHelper.BeginTransaction(SQLHelper.connectionString))
{
try
{
SQLHelper.ExecuteNonQuery(tran, CommandType.Text, sql.ToString());
tran.Commit();
Fun.Alert("初始化成功!");
}
catch
{
tran.Rollback();
Fun.Alert("初始化失败!");
}
}
}
private void GetDepositor(string AccountNumber)
{
depositorInfo.Style.Add("display", "none");
StorageList.Style.Add("display", "none");
string strSqlSimulate = "SELECT B.ISSimulate FROM dbo.Depositor A INNER JOIN dbo.WB B ON A.WBID=B.ID WHERE A.AccountNumber='" + AccountNumber + "' ";
object objSimulate = SQLHelper.ExecuteScalar(strSqlSimulate);
if (Convert.ToBoolean(objSimulate))
{
Fun.Alert("您当前查询的账户为模拟账户，不可以继续操作！");
return;
}
StringBuilder strSql = new StringBuilder();
strSql.Append(" select ID,WBID,AccountNumber,strPassword,CunID as BD_Address_CunID,strAddress,strName,PhoneNO,ISSendMessage,BankCardNO,dt_Update,");
strSql.Append(" CASE( numState) WHEN 1 THEN '正常' ELSE '挂失' END AS numState,dt_Add,");
strSql.Append(" CASE (IDCard) WHEN '' THEN '未填写' ELSE '******' END as IDCard");
strSql.Append(" FROM dbo.Depositor ");
strSql.Append(" where 1=1 ");
strSql.Append(" and AccountNumber=@AccountNumber ");
SqlParameter[] parameters =
{
new SqlParameter("@AccountNumber", SqlDbType.NVarChar, 50)
};
parameters[0].Value = AccountNumber;
DataTable dt = SQLHelper.ExecuteDataTable(strSql.ToString(), parameters);
if (dt != null && dt.Rows.Count != 0)
{
string numState = dt.Rows[0]["numState"].ToString();
if (numState == "0")
{
string StrScript;
StrScript = ("<script language=javascript>");
StrScript += ("alert('您查询的账户已经申请挂失!');");
StrScript += ("</script>");
System.Web.HttpContext.Current.Response.Write(StrScript);
return;
}
depositorInfo.Style.Add("display", "block");
D_strName.InnerText = dt.Rows[0]["strName"].ToString();
D_strAddress.InnerText = dt.Rows[0]["strAddress"].ToString();
D_PhoneNo.InnerText = dt.Rows[0]["PhoneNo"].ToString();
D_AccountNumber.InnerText = dt.Rows[0]["AccountNumber"].ToString();
D_numState.InnerText = dt.Rows[0]["numState"].ToString();
D_IDCard.InnerText = dt.Rows[0]["IDCard"].ToString();
this.Page.ClientScript.RegisterStartupScript(this.GetType(), "js", "ShowDepositorInfo()", true);
//获取存粮信息
StringBuilder strSqlStorage = new StringBuilder();
strSqlStorage.Append("SELECT A.ID,A.StorageNumber,convert(varchar(10),A.StorageDate,120) AS StorageDate, A.AccountNumber,B.strName AS VarietyID,A.Price_ShiChang,A.Price_DaoQi,A.CurrentRate,C.strName AS TimeID,A.StorageFee");
strSqlStorage.Append(" FROM dbo.Dep_StorageInfo A INNER JOIN dbo.StorageVariety B ON A.VarietyID=B.ID");
strSqlStorage.Append(" INNER JOIN dbo.StorageTime C ON A.TimeID=C.ID");
strSqlStorage.Append(" WHERE AccountNumber='" + AccountNumber + "'");
strSqlStorage.Append(" and A.StorageNumber>0");
DataTable dtStorage = SQLHelper.ExecuteDataTable(strSqlStorage.ToString());
if (dtStorage != null && dtStorage.Rows.Count != 0)
{
StorageList.Style.Add("display", "block");
Repeater1.DataSource = dtStorage;
Repeater1.DataBind();
}
//获取储户的存转销信息
StringBuilder strSqlEx = new StringBuilder();
strSqlEx.Append(" SELECT ID, BusinessName,CONVERT(varchar(100), dt_Sell, 111) AS dt_Sell,VarietyName,VarietyCount,StorageDate,VarietyInterest,VarietyMoney");
strSqlEx.Append(" FROM dbo.StorageSell ");
strSqlEx.Append(" WHERE 1=1");
strSqlEx.Append(" and ISReturn=0"); //查询没有退还记录的商品
strSqlEx.Append(" AND Dep_AccountNumber='" + QAccountNumber.Value.Trim() + "'");
strSqlEx.Append(" and ID NOT IN (SELECT StorageSellID FROM dbo.SA_Sell WHERE Dep_AN='" + QAccountNumber.Value.Trim() + "')"); //排除已经被结算的存转销
DataTable dtEx = SQLHelper.ExecuteDataTable(strSqlEx.ToString());
if (dtEx != null && dtEx.Rows.Count != 0)
{
divYes.Style.Add("display", "block");
divNO.Style.Add("display", "none");
divNO.Visible = false;
Repeater2.DataSource = dtEx;
Repeater2.DataBind();
}
else
{
divYes.Style.Add("display", "none");
divNO.Style.Add("display", "block");
}
}
else
{
string StrScript;
StrScript = ("<script language=javascript>");
StrScript += ("alert('您查询的储户不存在!');");
StrScript += ("</script>");
System.Web.HttpContext.Current.Response.Write(StrScript);
return;
}
}
private void DataBind(string AccountNumber)
{
depositorInfo.Style.Add("display", "none");
common.IsLogin();
string WBID = Session["WB_ID"].ToString();//当前网点ID
StringBuilder strSql = new StringBuilder();
strSql.Append(" select ID,WBID,AccountNumber,strPassword,ZuID,strAddress,strName,IDCard,PhoneNO,FieldCopies,FieldCount,ApplicationForm,CommunePic,dt_Commune,numState");
strSql.Append(" FROM dbo.Commune");
strSql.Append(" ");
strSql.Append(" ");
strSql.Append(" where 1=1 and numState=1 and WBID= " + WBID);
strSql.Append(" and AccountNumber=@AccountNumber ");
SqlParameter[] parameters =
{
new SqlParameter("@AccountNumber", SqlDbType.NVarChar, 50)
};
parameters[0].Value = AccountNumber;
DataTable dt = SQLHelper.ExecuteDataTable(strSql.ToString(), parameters);
if (dt != null && dt.Rows.Count != 0)
{
if (dt.Rows[0]["numState"].ToString() == "0")//该社员被禁用
{
Fun.Alert("该社员已被禁用，无法进行操作!");
return;
}
depositorInfo.Style.Add("display", "block");
D_strName.InnerText = dt.Rows[0]["strName"].ToString();
D_strAddress.InnerText = dt.Rows[0]["strAddress"].ToString();
D_PhoneNo.InnerText = dt.Rows[0]["PhoneNo"].ToString();
D_AccountNumber.InnerText = dt.Rows[0]["AccountNumber"].ToString();
D_FieldCopies.InnerText = dt.Rows[0]["FieldCopies"].ToString();
D_IDCard.InnerText = dt.Rows[0]["IDCard"].ToString();
}
StringBuilder strSqlCommune = new StringBuilder();
strSqlCommune.Append(" SELECT A.ID,A.BusinessName, B.strName AS WBID,C.strRealName AS UserID,A.C_AccountNumber,A.BusinessNO,A.VarietyID,A.UnitID,");
strSqlCommune.Append(" A.Price,A.Price_C,A.CountTrade, A.Money_Trade,A.Money_Reality,A.numDisCount,CONVERT(NVARCHAR(100),A.dt_Trade,23) AS dt_Trade");
strSqlCommune.Append(" FROM dbo.C_OperateLog A INNER JOIN WB B ON A.WBID=B.ID");
strSqlCommune.Append(" INNER JOIN dbo.Users C ON A.UserID=C.ID");
strSqlCommune.Append(" ");
strSqlCommune.Append(" where 1=1");
strSqlCommune.Append(" AND A.C_AccountNumber = '" + AccountNumber + "'");
if (Qdtstart.Value.Trim() != "")
{
strSqlCommune.Append(" AND A.dt_Trade> '" + Qdtstart.Value.Trim() + "'");
}
if (Qdtend.Value.Trim() != "")
{
strSqlCommune.Append(" AND A.dt_Trade < '" + Qdtend.Value.Trim() + "'");
}
strSqlCommune.Append(" order by A.dt_Trade desc");
strSql.Append(" ");
DataTable dtCommune = SQLHelper.ExecuteDataTable(strSqlCommune.ToString());
Repeater1.DataSource = dtCommune;
Repeater1.DataBind();
}
protected void btnInitStorage_Click(object sender, EventArgs e)
{
if (Session["isCheck"] == null || Convert.ToBoolean(Session["isCheck"]) == false)
{
return;
}
StringBuilder sqlStorageInfo = new StringBuilder();
sqlStorageInfo.Append(" SELECT WBID AS 网点,VarietyID AS 粮食品种, SUM(StorageNumberRaw) AS 存储量");
sqlStorageInfo.Append(" FROM dbo.Dep_StorageInfo");
sqlStorageInfo.Append(" GROUP BY VarietyID,WBID");
DataTable dtDep_StorageInfo = SQLHelper.ExecuteDataTable(sqlStorageInfo.ToString());
StringBuilder sqlCheckOut = new StringBuilder();
sqlCheckOut.Append(" SELECT WBID AS 网点,VarietyID AS 粮食品种, SUM(Count_Trade) AS 出库量");
sqlCheckOut.Append(" FROM dbo.SA_CheckOutLog");
sqlCheckOut.Append(" GROUP BY VarietyID,WBID");
DataTable dtCheckOut = SQLHelper.ExecuteDataTable(sqlCheckOut.ToString());
StringBuilder sql = new StringBuilder();
sql.Append(" DELETE FROM dbo.SA_VarietyStorageLog");
sql.Append(" ");
for (int i = 0; i < dtDep_StorageInfo.Rows.Count; i++)
{
int WBID = Convert.ToInt32(dtDep_StorageInfo.Rows[i]["网点"]);
int VarietyID = Convert.ToInt32(dtDep_StorageInfo.Rows[i]["粮食品种"]);
double numStorageIn = Convert.ToDouble(dtDep_StorageInfo.Rows[i]["存储量"]);
double numStorageOut = 0;
double numStorage = 0;
double numStorageChange = 0;
double numStorageLoss = 0;
DataView dv = dtCheckOut.DefaultView;
dv.RowFilter = "网点=" + WBID + " and 粮食品种=" + VarietyID;
if (dv.Count > 0)
{
DataRow row = dv.ToTable().Rows[0];
numStorageOut = -Convert.ToDouble(row["出库量"]);//出库量取负数
}
numStorage = numStorageIn + numStorageOut;
sql.Append(" INSERT INTO dbo.SA_VarietyStorageLog");
sql.Append(" ( storageType , OperateLogID , CheckOutID , AccountNumber , VarietyID ,WBID , numStorage, numStorageChange, numStorageIn ,numStorageOut ,numStorageLoss , WareHouseID ,ISHQ , ISSimulate,dtLog)");
sql.Append(" VALUES ( 0 ,0 , 0 , N'' , " + VarietyID + " , " + WBID + " , ");
sql.Append(" " + numStorage + " ," + numStorageChange + " , " + numStorageIn + " , " + numStorageOut + " , " + numStorageLoss + " ,");
sql.Append(" 0 , 0 , 0 ,'" + DateTime.Now.ToString() + "' )");
}
using (SqlTransaction tran = SQLHelper.BeginTransaction(SQLHelper.connectionString))
{
try
{
SQLHelper.ExecuteNonQuery(tran, CommandType.Text, sql.ToString());
tran.Commit();
Fun.Alert("初始化成功!");
}
catch
{
tran.Rollback();
Fun.Alert("初始化失败!");
}
}
}
private void outputExcel()
{
string WBName = txtWBID.Value.ToString();
//获取存粮信息
StringBuilder strSql = new StringBuilder();
strSql.Append(" SELECT B.strName AS WBName,A.SA_AN,V.strName AS Variety_Name,Weight_Mao,Weight_Pi,Weight_Jing,Weight_Reality,CONVERT(VARCHAR(100),dt_Trade,23) AS dt_Trade");
strSql.Append(" FROM dbo.SA_CheckOut A INNER JOIN WB B ON A.WBID=B.ID");
strSql.Append(" INNER JOIN dbo.StorageVariety V ON A.Variety_Name=V.ID");
if (WBName.ToString().Trim() != "")
{
strSql.Append(" WHERE B.strName = '" + WBName + "'");
}
strSql.Append(" ORDER BY A.dt_Trade DESC");
StringBuilder strSqlTotal = new StringBuilder();
strSqlTotal.Append(" SELECT B.strName AS WBName,V.strName AS Variety_Name,");
strSqlTotal.Append(" SUM( Weight_Mao) AS Weight_Mao, SUM( Weight_Pi) AS Weight_Pi, SUM( Weight_Jing) AS Weight_Jing, SUM( Weight_Reality) AS Weight_Reality");
strSqlTotal.Append(" FROM dbo.SA_CheckOut A INNER JOIN WB B ON A.WBID=B.ID");
strSqlTotal.Append(" INNER JOIN dbo.StorageVariety V ON A.Variety_Name=V.ID");
if (WBName.ToString().Trim() != "")
{
strSqlTotal.Append(" WHERE B.strName = '" + WBName + "'");
}
strSqlTotal.Append(" GROUP BY B.strName,V.strName");
DataTable dtTotal = SQLHelper.ExecuteDataTable(strSqlTotal.ToString());
DataTable dt = SQLHelper.ExecuteDataTable(strSql.ToString());
if (dt == null || dt.Rows.Count == 0)
{
Fun.Alert("请先检索要查询的信息后在导出！");
return;
}
string fileName = "网点出库记录" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
HttpResponse resp;
resp = Page.Response;
resp.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
resp.AppendHeader("Content-Disposition", "attachment;filename=" + fileName);
string colHeaders = "", ls_item = "", ls_Bottom = "";
DataRow[] myRow = dt.Select();//可以类似dt.Select("id>10")之形式达到数据筛选目的
int i = 0;
int cl = dt.Columns.Count;
colHeaders += "网点名称\t网点账号\t产品名称\t毛重\t皮重\t净重\t出库实重\t出库时间\n";
resp.Write(colHeaders);
//向HTTP输出流中写入取得的数据信息
//逐行处理数据
foreach (DataRow row in myRow)
{
//当前行数据写入HTTP输出流，并且置空ls_item以便下行数据
for (i = 0; i < cl; i++)
{
if (i == (cl - 1))//最后一列，加\n
{
ls_item += row[i].ToString() + "\n";
}
else
{
ls_item += row[i].ToString() + "\t";
}
}
resp.Write(ls_item);
ls_item = "";
}
resp.Write("总计\n");
myRow = dtTotal.Select();//可以类似dt.Select("id>10")之形式达到数据筛选目的
i = 0;
cl = dtTotal.Columns.Count;
foreach (DataRow row in myRow)
{
//当前行数据写入HTTP输出流，并且置空ls_item以便下行数据
for (i = 0; i < cl; i++)
{
if (i == 1)
{ //网点账号一列不写入数据
ls_item += "\t";
}
if (i == (cl - 1))//最后一列，加\n
{
ls_item += row[i].ToString() + "\n";
}
else
{
ls_item += row[i].ToString() + "\t";
}
}
resp.Write(ls_item);
ls_item = "";
}
resp.End();
}
protected void btnInitDep_SID_Click(object sender, EventArgs e)
{
string sqlLog = " SELECT ID,BusinessNO, BusinessName,Dep_AccountNumber FROM dbo.Dep_OperateLog ";
DataTable dt = SQLHelper.ExecuteDataTable(sqlLog);
if (dt == null || dt.Rows.Count == 0)
{
return;
}
StringBuilder sql = new StringBuilder();
for (int i = 0; i < dt.Rows.Count; i++)
{
string ID = dt.Rows[i]["ID"].ToString();
string BusinessNO = dt.Rows[i]["BusinessNO"].ToString();
string BusinessName = dt.Rows[i]["BusinessName"].ToString();
string Dep_AccountNumber = dt.Rows[i]["Dep_AccountNumber"].ToString();
string Dep_SID = "0";
object obj;
switch (BusinessName)
{
case "1":
case "5":
case "8": //存粮,修改错误存粮,退还存粮
obj = SQLHelper.ExecuteScalar(string.Format(" SELECT TOP 1 ID FROM dbo.Dep_StorageInfo WHERE BusinessNO='{0}' AND AccountNumber='{1}'", BusinessNO, Dep_AccountNumber));
if (obj != null && obj.ToString() != "")
{
sql.Append(string.Format(" UPDATE dbo.Dep_OperateLog SET Dep_SID={0} WHERE BusinessNO='{1}' AND Dep_AccountNumber='{2}'", obj.ToString(), BusinessNO, Dep_AccountNumber));
}
break;
case "2":
case "6": //兑换、退还兑换
obj = SQLHelper.ExecuteScalar(string.Format(" SELECT TOP 1 Dep_SID FROM dbo.GoodExchange WHERE BusinessNO='{0}' AND Dep_AccountNumber='{1}'", BusinessNO, Dep_AccountNumber));
if (obj != null && obj.ToString() != "")
{
sql.Append(string.Format(" UPDATE dbo.Dep_OperateLog SET Dep_SID={0} WHERE BusinessNO='{1}' AND Dep_AccountNumber='{2}'", obj.ToString(), BusinessNO, Dep_AccountNumber));
}
break;
case "3":
case "7": //存转销、退还存转销
obj = SQLHelper.ExecuteScalar(string.Format(" SELECT TOP 1 Dep_SID FROM dbo.StorageSell WHERE BusinessNO='{0}' AND Dep_AccountNumber='{1}'", BusinessNO, Dep_AccountNumber));
if (obj != null && obj.ToString() != "")
{
sql.Append(string.Format(" UPDATE dbo.Dep_OperateLog SET Dep_SID={0} WHERE BusinessNO='{1}' AND Dep_AccountNumber='{2}'", obj.ToString(), BusinessNO, Dep_AccountNumber));
}
break;
case "9":
case "10": //换购、退还换购
obj = SQLHelper.ExecuteScalar(string.Format(" SELECT TOP 1 Dep_SID FROM dbo.StorageShopping WHERE BusinessNO='{0}' AND Dep_AccountNumber='{1}'", BusinessNO, Dep_AccountNumber));
if (obj != null && obj.ToString() != "")
{
sql.Append(string.Format(" UPDATE dbo.Dep_OperateLog SET Dep_SID={0} WHERE BusinessNO='{1}' AND Dep_AccountNumber='{2}'", obj.ToString(), BusinessNO, Dep_AccountNumber));
}
break;
case "11": //结息
obj = SQLHelper.ExecuteScalar(string.Format(" SELECT TOP 1 Dep_SID FROM dbo.StorageInterest WHERE BusinessNO='{0}' AND Dep_AccountNumber='{1}'", BusinessNO, Dep_AccountNumber));
if (obj != null && obj.ToString() != "")
{
sql.Append(string.Format(" UPDATE dbo.Dep_OperateLog SET Dep_SID={0} WHERE BusinessNO='{1}' AND Dep_AccountNumber='{2}'", obj.ToString(), BusinessNO, Dep_AccountNumber));
}
break;
}
}
using (SqlTransaction tran = SQLHelper.BeginTransaction(SQLHelper.connectionString))
{
try
{
SQLHelper.ExecuteNonQuery(tran, CommandType.Text, sql.ToString());//将储户存粮置0
tran.Commit();
Fun.Alert("初始化成功！");
}
catch
{
tran.Rollback();
Fun.Alert("初始化失败！");
}
}
}
private void outputExcel()
{
StringBuilder strSql = new StringBuilder();
strSql.Append(" SELECT B.strName AS WBName, C.strName AS VarietyName,D.strName AS VarietyLevelName,A.numStorage");
strSql.Append(" FROM dbo.SA_VarietyStorage A INNER JOIN WB B ON A.WBID=B.ID");
strSql.Append(" LEFT OUTER JOIN dbo.StorageVariety C ON A.VarietyID=C.ID");
strSql.Append(" LEFT OUTER JOIN dbo.StorageVarietyLevel_B D ON A.VarietyLevelID=D.ID");
if (txtWBID.Value.Trim() != "")
{
strSql.Append(" WHERE B.strName = '" + txtWBID.Value + "'");
}
strSql.Append(" order by B.strName");
DataTable dt = SQLHelper.ExecuteDataTable(strSql.ToString());
if (dt == null || dt.Rows.Count == 0)
{
Fun.Alert("请先检索要查询的信息后在导出！");
return;
}
string fileName = "网点库存" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
HttpResponse resp;
resp = Page.Response;
resp.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
resp.AppendHeader("Content-Disposition", "attachment;filename=" + fileName);
string colHeaders = "", ls_item = "", ls_Bottom = "";
DataRow[] myRow = dt.Select();//可以类似dt.Select("id>10")之形式达到数据筛选目的
int i = 0;
int cl = dt.Columns.Count;
colHeaders += "网点\t品种\t等级\t库存\n";
resp.Write(colHeaders);
//向HTTP输出流中写入取得的数据信息
//逐行处理数据
foreach (DataRow row in myRow)
{
//当前行数据写入HTTP输出流，并且置空ls_item以便下行数据
for (i = 0; i < cl; i++)
{
if (i == (cl - 1))//最后一列，加\n
{
ls_item += row[i].ToString() + "\n";
}
else
{
ls_item += row[i].ToString() + "\t";
}
}
resp.Write(ls_item);
ls_item = "";
}
resp.End();
}
private void GetDepositor(string AccountNumber)
{
depositorInfo.Style.Add("display", "none");
common.IsLogin();
string WBID = Session["WB_ID"].ToString();//当前网点ID
StringBuilder strSql = new StringBuilder();
strSql.Append(" select ID,WBID,AccountNumber,strPassword, CunID as BD_Address_CunID,strAddress,strName,PhoneNO,ISSendMessage,BankCardNO,dt_Update,");
strSql.Append(" CASE( numState) WHEN 1 THEN '正常' ELSE '挂失' END AS numState,dt_Add,");
strSql.Append(" CASE (IDCard) WHEN '' THEN '未填写' ELSE '******' END as IDCard");
strSql.Append(" FROM dbo.Depositor ");
strSql.Append(" where 1=1 ");
//if (!ISHQ)
//{
// strSql.Append(" and WBID= " + WBID);
//}
if (!ISHQ)
{
if (Convert.ToBoolean(common.GetWBAuthority()["Enable_Distance"]) == false)//不允许异地存取的情况
{
strSql.Append(" and WBID= " + WBID);
}
}
strSql.Append(" and AccountNumber=@AccountNumber ");
SqlParameter[] parameters =
{
new SqlParameter("@AccountNumber", SqlDbType.NVarChar, 50)
};
parameters[0].Value = AccountNumber;
DataTable dt = SQLHelper.ExecuteDataTable(strSql.ToString(), parameters);
if (dt != null && dt.Rows.Count != 0)
{
if (!common.CheckPassword(AccountNumber, QPassword.Value.Trim()))
{
Fun.Alert("储户密码错误，请重新输入!");
return;
}
depositorInfo.Style.Add("display", "block");
D_strName.InnerText = dt.Rows[0]["strName"].ToString();
D_strAddress.InnerText = dt.Rows[0]["strAddress"].ToString();
D_PhoneNo.InnerText = dt.Rows[0]["PhoneNo"].ToString();
D_AccountNumber.InnerText = dt.Rows[0]["AccountNumber"].ToString();
D_numState.InnerText = dt.Rows[0]["numState"].ToString();
D_IDCard.InnerText = dt.Rows[0]["IDCard"].ToString();
//获取存粮信息
StringBuilder strSqlStorage = new StringBuilder();
strSqlStorage.Append("SELECT A.ID,A.StorageNumber,A.StorageDate, A.AccountNumber,A.WeighNo,B.strName AS VarietyID,A.Price_ShiChang,A.Price_DaoQi,C.strName AS TimeID,A.StorageFee");
strSqlStorage.Append(" FROM dbo.Dep_StorageInfo A INNER JOIN dbo.StorageVariety B ON A.VarietyID=B.ID");
strSqlStorage.Append(" INNER JOIN dbo.StorageTime C ON A.TimeID=C.ID");
strSqlStorage.Append(" WHERE AccountNumber='" + AccountNumber + "'");
strSqlStorage.Append(" and A.StorageNumber>0");
if (!ISHQ)
{
strSqlStorage.Append(" and DATEDIFF(DAY,A.StorageDate,GETDATE())<1 ");
}
DataTable dtStorage = SQLHelper.ExecuteDataTable(strSqlStorage.ToString());
if (dtStorage != null && dtStorage.Rows.Count != 0)
{
Repeater1.DataSource = dtStorage;
Repeater1.DataBind();
}
else
{
StorageInfo.InnerText = "该储户今天没有存储信息，不需要修改";
Repeater1.DataSource = null;
Repeater1.DataBind();
}
}
else
{
string StrScript;
StrScript = ("<script language=javascript>");
StrScript += ("alert('您查询的储户不存在!');");
StrScript += ("</script>");
System.Web.HttpContext.Current.Response.Write(StrScript);
return;
}
}
private void outputExcel()
{
DataTable dt = GetoutputTable();
if (dt == null || dt.Rows.Count == 0)
{
Fun.Alert("请先检索要查询的信息后在导出！");
return;
}
string fileName = "业务统计" + DateTime.Now.Year + DateTime.Now.Month + DateTime.Now.Day + ".xls";
HttpResponse resp;
resp = Page.Response;
resp.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
resp.AppendHeader("Content-Disposition", "attachment;filename=" + fileName);
string colHeaders = "", ls_item = "", ls_Bottom = "";
DataRow[] myRow = dt.Select();//可以类似dt.Select("id>10")之形式达到数据筛选目的
int i = 0;
int cl = dt.Columns.Count;
colHeaders += "网点\t商品种类\t商品单价\t计量单位\t购买数量\t商品总价\t优惠金额\t预付款金额\t实付金额\t返利金额\n";
resp.Write(colHeaders);
//向HTTP输出流中写入取得的数据信息
//逐行处理数据
foreach (DataRow row in myRow)
{
//当前行数据写入HTTP输出流，并且置空ls_item以便下行数据
for (i = 0; i < cl; i++)
{
if (i == (cl - 1))//最后一列，加\n
{
ls_item += row[i].ToString() + "\n";
}
else
{
ls_item += row[i].ToString() + "\t";
}
}
resp.Write(ls_item);
ls_item = "";
}
//添加第二张表格的内容
StringBuilder strSql2 = new StringBuilder();
strSql2.Append(" SELECT '', C.strName AS GoodSupplyID, '','', ");
strSql2.Append(" SUM(GoodSupplyCount)AS GoodSupplyCount,SUM(Money_Total) AS Money_Total,SUM(Money_YouHui) AS Money_YouHui,SUM(Money_PreDefine) AS Money_PreDefine,SUM(Money_Reality) AS Money_Reality,SUM(Money_Back) AS Money_Back");
strSql2.Append(" FROM dbo.C_Supply A INNER JOIN dbo.GoodSupply C ON A.GoodSupplyID=C.ID");
strSql2.Append(" INNER JOIN dbo.WB D ON A.WBID=D.ID");
strSql2.Append(" where 1=1 and D.ISSimulate=0");
if (Request.Form["QWBID"] != null && Request.Form["QWBID"].ToString() != "")
{
strSql2.Append(" AND A.WBID = " + Request.Form["QWBID"].ToString());
}
if (Qdtstart.Value.Trim() != "")
{
strSql2.Append(" AND dt_Trade> '" + Qdtstart.Value.Trim() + "'");
}
if (Qdtend.Value.Trim() != "")
{
strSql2.Append(" AND dt_Trade < '" + Qdtend.Value.Trim() + "'");
}
strSql2.Append(" GROUP BY C.strName");
DataTable dt2 = SQLHelper.ExecuteDataTable(strSql2.ToString());
DataRow[] myRow2 = dt2.Select();//可以类似dt.Select("id>10")之形式达到数据筛选目的
int cl2 = dt2.Columns.Count;
colHeaders = "总计\n";
resp.Write(colHeaders);
//向HTTP输出流中写入取得的数据信息
ls_item = "";
//逐行处理数据
foreach (DataRow row in myRow2)
{
//当前行数据写入HTTP输出流，并且置空ls_item以便下行数据
for (int j = 0; j < cl2; j++)
{
if (j == (cl2 - 1))//最后一列，加\n
{
ls_item += row[j].ToString() + "\n";
}
else
{
ls_item += row[j].ToString() + "\t";
}
}
resp.Write(ls_item);
ls_item = "";
}
resp.End();
}
/// <summary>
/// 由账号查询社员信息
/// </summary>
/// <param name="AccountNumber"></param>
private void GetDepositor(string AccountNumber)
{
depositorInfo.Style.Add("display", "none");
Supply.Style.Add("display", "none");
divPreDefine.Style.Add("display", "none");
divfrm.Style.Add("display", "none");
common.IsLogin();
string WBID = Session["WB_ID"].ToString();//当前网点ID
StringBuilder strSql = new StringBuilder();
strSql.Append(" select ID,WBID,AccountNumber,strPassword,ZuID,strAddress,strName,IDCard,PhoneNO,FieldCopies,FieldCount,ApplicationForm,CommunePic,dt_Commune,numState");
strSql.Append(" FROM dbo.Commune");
strSql.Append(" where 1=1 and numState=1");
if (Convert.ToBoolean(common.GetWBAuthority()["Enable_Distance"]) == false)//不允许异地存取的情况
{
strSql.Append(" and WBID= " + WBID);
}
strSql.Append(" and AccountNumber=@AccountNumber ");
SqlParameter[] parameters =
{
new SqlParameter("@AccountNumber", SqlDbType.NVarChar, 50)
};
parameters[0].Value = AccountNumber;
DataTable dt = SQLHelper.ExecuteDataTable(strSql.ToString(), parameters);
if (dt != null && dt.Rows.Count != 0)
{
//if (!common.CheckPassword_Commune(AccountNumber, QPassword.Value.Trim()))
//{
// Fun.Alert("储户密码错误，请重新输入!");
// return;
//}
if (dt.Rows[0]["numState"].ToString() == "0")//该社员被禁用
{
Fun.Alert("该社员已被禁用，无法进行操作!");
return;
}
txtC_AccountNumber.Value = AccountNumber;
depositorInfo.Style.Add("display", "block");
divfrm.Style.Add("display", "block");
D_strName.InnerText = dt.Rows[0]["strName"].ToString();
txtC_Name.Value = dt.Rows[0]["strName"].ToString();
D_strAddress.InnerText = dt.Rows[0]["strAddress"].ToString();
D_PhoneNo.InnerText = dt.Rows[0]["PhoneNo"].ToString();
D_AccountNumber.InnerText = dt.Rows[0]["AccountNumber"].ToString();
D_FieldCopies.InnerText = dt.Rows[0]["FieldCopies"].ToString();
D_IDCard.InnerText = dt.Rows[0]["IDCard"].ToString();
//历史交易信息
StringBuilder strSqlSupply = new StringBuilder();
strSqlSupply.Append(" select ID,GoodSupplyName,SpecName,UnitName,GoodSupplyCount,GoodSupplyPrice,Price_Commune,");
strSqlSupply.Append(" Money_Total,Money_YouHui,Money_PreDefine,Money_Reality, CONVERT(NVARCHAR(100),dt_Trade,23) AS dt_Trade ");
strSqlSupply.Append(" FROM dbo.C_Supply ");
strSqlSupply.Append(" ");
strSqlSupply.Append(" WHERE C_AccountNumber='" + AccountNumber + "'");
strSqlSupply.Append(" ORDER BY dt_Trade DESC");
DataTable dtSupply = SQLHelper.ExecuteDataTable(strSqlSupply.ToString());
if (dtSupply != null && dtSupply.Rows.Count != 0)
{
Supply.Style.Add("display", "block");
Repeater1.DataSource = dtSupply;
Repeater1.DataBind();
}
//预存款信息
StringBuilder strSqlStorage = new StringBuilder();
strSqlStorage.Append(" SELECT top 1 A.ID,B.strRealName AS UserID,A.C_AccountNumber,A.Money_PreDefine,C.strName AS GoodSupplyID,CONVERT(varchar(100), A.dt_Trade, 23) AS dt_Trade,DATEDIFF(DAY,A.dt_Trade,GETDATE()) AS numday");
strSqlStorage.Append(" FROM dbo.C_PreDefine A INNER JOIN dbo.Users B ON A.UserID=B.ID");
strSqlStorage.Append(" INNER JOIN dbo.GoodSupply C ON A.GoodSupplyID=C.ID");
strSqlStorage.Append(" WHERE ISUsed=0 and C_AccountNumber='" + AccountNumber + "'");
DataTable dtStorage = SQLHelper.ExecuteDataTable(strSqlStorage.ToString());
if (dtStorage != null && dtStorage.Rows.Count != 0)
{
divPreDefine.Style.Add("display", "block");
RPreDefine.DataSource = dtStorage;
RPreDefine.DataBind();
}
}
else
{
Fun.Alert("您查询的社员不存在");
return;
}
}
private void GetDepositor(string AccountNumber)
{
depositorInfo.Style.Add("display", "none");
common.IsLogin();
string WBID = Session["WB_ID"].ToString();//当前网点ID
StringBuilder strSql = new StringBuilder();
strSql.Append(" select ID,WBID,AccountNumber,strPassword,ZuID,strAddress,strName,IDCard,PhoneNO,FieldCopies,FieldCount,ApplicationForm,CommunePic,dt_Commune,numState");
strSql.Append(" FROM dbo.Commune");
strSql.Append(" where 1=1 and numState=1");
if (Convert.ToBoolean(common.GetWBAuthority()["Enable_Distance"]) == false)//不允许异地存取的情况
{
strSql.Append(" and WBID= " + WBID);
}
strSql.Append(" and AccountNumber=@AccountNumber ");
SqlParameter[] parameters =
{
new SqlParameter("@AccountNumber", SqlDbType.NVarChar, 50)
};
parameters[0].Value = AccountNumber;
DataTable dt = SQLHelper.ExecuteDataTable(strSql.ToString(), parameters);
if (dt != null && dt.Rows.Count != 0)
{
if (!common.CheckPassword_Commune(AccountNumber, QPassword.Value.Trim()))
{
Fun.Alert("储户密码错误，请重新输入!");
return;
}
if (dt.Rows[0]["numState"].ToString() == "0")//该社员被禁用
{
Fun.Alert("该社员已被禁用，无法进行操作!");
return;
}
depositorInfo.Style.Add("display", "block");
D_strName.InnerText = dt.Rows[0]["strName"].ToString();
D_strAddress.InnerText = dt.Rows[0]["strAddress"].ToString();
D_PhoneNo.InnerText = dt.Rows[0]["PhoneNo"].ToString();
D_AccountNumber.InnerText = dt.Rows[0]["AccountNumber"].ToString();
D_FieldCopies.InnerText = dt.Rows[0]["FieldCopies"].ToString();
D_IDCard.InnerText = dt.Rows[0]["IDCard"].ToString();
//获取社员当前的购买信息
StringBuilder strReturn = new StringBuilder();
strReturn.Append(" SELECT ID, GoodSupplyName,UnitName,GoodSupplyPrice,GoodSupplyCount,Money_YouHui,(Money_Total-Money_YouHui) AS Money_Return");
strReturn.Append(" FROM dbo.C_Supply ");
strReturn.Append(" WHERE C_AccountNumber='" + AccountNumber + "' AND DATEDIFF(DAY,dt_Trade,GETDATE())<1");
strReturn.Append(" ");
strReturn.Append(" ");
DataTable dtReturn = SQLHelper.ExecuteDataTable(strReturn.ToString());
Repeater2.DataSource = dtReturn;
Repeater2.DataBind();
}
else
{
Fun.Alert("您查询的社员不存在");
return;
}
}
private void outputExcel()
{
DataTable dt = GetoutputTable();
if (dt == null || dt.Rows.Count == 0)
{
Fun.Alert("请先检索要查询的信息后在导出！");
return;
}
string fileName = "社员信息" + DateTime.Now.Year + DateTime.Now.Month + DateTime.Now.Day + ".xls";
HttpResponse resp;
resp = Page.Response;
resp.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
resp.AppendHeader("Content-Disposition", "attachment;filename=" + fileName);
string colHeaders = "", ls_item = "", ls_Bottom = "";
DataRow[] myRow = dt.Select();//可以类似dt.Select("id>10")之形式达到数据筛选目的
int i = 0;
int cl = dt.Columns.Count;
//取得数据表各列标题，各标题之间以\t分割，最后一个列标题后加回车符
//for (i = 0; i < cl; i++)
//{
// if (i == (cl - 1))//最后一列，加\n
// {
// colHeaders += dt.Columns[i].Caption.ToString() + "\n";
// }
// else
// {
// colHeaders += dt.Columns[i].Caption.ToString() + "\t";
// }
//}
colHeaders += "网点\t社员账号\t地址\t姓名\t身份证号\t手机号\t田亩册\t田亩数\t入社时间\n";
resp.Write(colHeaders);
//向HTTP输出流中写入取得的数据信息
//逐行处理数据
foreach (DataRow row in myRow)
{
//当前行数据写入HTTP输出流，并且置空ls_item以便下行数据
for (i = 0; i < cl; i++)
{
if (i == (cl - 1))//最后一列，加\n
{
ls_item += row[i].ToString() + "\n";
}
else
{
ls_item += row[i].ToString() + "\t";
}
}
resp.Write(ls_item);
ls_item = "";
}
GetCount();//再次获取统计数目
ls_Bottom += "统计\t社员数:" + IDCount + "\t\t\t\t\t田亩数:\t" + FieldCount + "\t\n";
resp.Write(ls_Bottom);
resp.End();
}
private void BindData(string WBID, string dtStart, string dtEnd)
{
//当前网点最少要存储满多少天可以给网点代理费
DataTable dtWB = SQLHelper.ExecuteDataTable(" SELECT top 1 numDay,draw_exchange,draw_sell,draw_shopping FROM dbo.WB WHERE ID =" + WBID);
if (dtWB == null || dtWB.Rows.Count == 0)
{
Fun.Alert("查询该网点信息失败！");
return;
}
int numDay = Convert.ToInt32(dtWB.Rows[0]["numDay"]);
int numMinute = numDay * 24 * 60;//提前支取计算的分钟数
int draw_exchange = Convert.ToInt32(dtWB.Rows[0]["draw_exchange"]);
int draw_sell = Convert.ToInt32(dtWB.Rows[0]["draw_sell"]);
int draw_shopping = Convert.ToInt32(dtWB.Rows[0]["draw_shopping"]);
string buslist = "";
if (draw_exchange == 1)
{
buslist += "," + "'2','6'";
}
if (draw_sell == 1)
{
buslist += "," + "'3','7'";
}
if (draw_shopping == 1)
{
buslist += "," + "'9','10'";
}
if (buslist != "")
{
buslist = buslist.Substring(1);
}
//获取存粮信息
StringBuilder strSql = new StringBuilder();
StringBuilder strSqlPara = new StringBuilder();//查询参数
strSql.Append(" SELECT F.ID AS AgentFeeID, A.ID,A.VarietyID ,V.strName AS VarietyName, C.strName AS WBName,B.AccountNumber,B.strName,'存粮代理' AS BusinessName,CONVERT(NVARCHAR(100),A.StorageDate,23 ) AS StorageDate,");
strSql.Append(" A.StorageDate as StorageTime, ");
strSql.Append(" DATEDIFF(DAY,A.StorageDate,GETDATE()) AS StorageDay, A.StorageNumberRaw AS CunRu, 0 as zhiqu,A.StorageNumberRaw AS shicun, C.numAgent,A.StorageNumberRaw*C.numAgent AS MoenyFee ");
strSql.Append(" FROM dbo.Dep_StorageInfo A INNER JOIN dbo.Depositor B ON A.AccountNumber=B.AccountNumber ");
strSql.Append(" LEFT OUTER JOIN dbo.StorageVariety V ON A.VarietyID=V.ID");
strSql.Append(" LEFT OUTER JOIN dbo.SA_AgentFee F ON A.ID=F.Dep_StorageInfoID");
strSql.Append(" INNER JOIN dbo.WB C ON B.WBID=C.ID ");
strSqlPara.Append(" WHERE DATEDIFF(minute,A.StorageDate,GETDATE())>" + numMinute);
if (WBID != "" && WBID != "0")
{
strSqlPara.Append(" AND A.WBID = " + WBID);
}
if (dtStart != "")
{
strSqlPara.Append(" AND A.StorageDate> '" + dtStart + "'");
}
if (dtEnd != "")
{
if (Fun.IsDateTime(dtEnd))
{
dtEnd = Convert.ToDateTime(dtEnd).AddDays(1).ToString();
}
strSqlPara.Append(" AND A.StorageDate < '" + dtEnd + "'");
}
strSql.Append(strSqlPara.ToString());
strSql.Append(" order by A.StorageDate desc");
DataTable dt = SQLHelper.ExecuteDataTable(strSql.ToString());
numCunRu = 0; numZhiQu = 0; numShiCun = 0; numMoenyFee = 0;
if (dt != null && dt.Rows.Count != 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
if (buslist != "")
{
double zhiqu = 0;
double shicun = 0;
double cunru = Convert.ToDouble(dt.Rows[i]["CunRu"]);
double numAgent = Convert.ToDouble(dt.Rows[i]["numAgent"]);
double MoenyFee = Convert.ToDouble(dt.Rows[i]["MoenyFee"]);
string ID = dt.Rows[i]["ID"].ToString();
//string AccountNumber = dt.Rows[i]["AccountNumber"].ToString();
//string VarietyID = dt.Rows[i]["VarietyID"].ToString();
//string StorageDate = dt.Rows[i]["StorageDate"].ToString();
string StorageTime = dt.Rows[i]["StorageTime"].ToString();//储户的准确存储时间
//string sql = string.Format(" SELECT SUM(Count_Trade) FROM dbo.Dep_OperateLog WHERE BusinessName IN ({0}) AND Dep_AccountNumber='{1}' AND VarietyID='{2}' AND DATEDIFF(minute,'{3}',dt_Trade)<={4}", buslist, AccountNumber, VarietyID, StorageTime, numMinute);
string sql = string.Format(" SELECT SUM(Count_Trade) FROM dbo.Dep_OperateLog WHERE BusinessName IN ({0}) AND Dep_SID={1} AND DATEDIFF(minute,'{2}',dt_Trade)<={3}", buslist, ID, StorageTime, numMinute);
object obj_zhiqu = SQLHelper.ExecuteScalar(sql);
if (obj_zhiqu != null && obj_zhiqu.ToString() != "")
{
zhiqu = Convert.ToDouble(obj_zhiqu);
}
shicun = cunru - zhiqu;
MoenyFee = shicun * numAgent;
dt.Rows[i]["zhiqu"] = Math.Round(zhiqu, 2);
dt.Rows[i]["shicun"] = Math.Round(shicun, 2);
dt.Rows[i]["MoenyFee"] = Math.Round(MoenyFee, 2);
}
object AgentFeeID = dt.Rows[i]["AgentFeeID"];
if (AgentFeeID == null || AgentFeeID.ToString() == "")
{
numCunRu += Convert.ToDouble(dt.Rows[i]["CunRu"]);
numZhiQu += Convert.ToDouble(dt.Rows[i]["zhiqu"]);
numShiCun += Convert.ToDouble(dt.Rows[i]["shicun"]);
numMoenyFee += Convert.ToDouble(dt.Rows[i]["MoenyFee"]);
}
else
{
JieSuan += Convert.ToDouble(dt.Rows[i]["MoenyFee"]);
}
}
numCunRu = Math.Round(numCunRu, 2);
numZhiQu = Math.Round(numZhiQu, 2);
numShiCun = Math.Round(numShiCun, 2);
numMoenyFee = Math.Round(numMoenyFee, 2);
JieSuan = Math.Round(JieSuan, 2);
}
Repeater1.DataSource = dt;
Repeater1.DataBind();
}
void UserLogin(HttpContext context)
{
var userType = context.Request.QueryString["user"];//index 普通用户账号;adminindex:单位管理员;sysadminindex:系统管理员
var LoginName = context.Request.Form["LoginName"];
var Password = context.Request.Form["Password"];
if (LoginName.Trim() == "")
{
context.Response.Write("请输入登录名!");
return;
}
if (Password.Trim() == "")
{
context.Response.Write("请输入密码!");
return;
}
//检验账户正确性
StringBuilder strAccount = new StringBuilder();
strAccount.Append(" SELECT ID, WB_ID,UserGroup_ID,strLoginName,strRealName,strPassword,ISEnable from Users");
strAccount.Append(" WHERE LOWER(strLoginName)=@strLoginName ");
SqlParameter[] para = new SqlParameter[] {
new SqlParameter("@strLoginName", LoginName.Trim().ToLower())
};
DataTable dtAccount = SQLHelper.ExecuteDataTable(strAccount.ToString(), para);
if (dtAccount != null && dtAccount.Rows.Count != 0)
{
int UserGroup_ID = Convert.ToInt32(dtAccount.Rows[0]["UserGroup_ID"]);
string UserGroupName = SQLHelper.ExecuteScalar(" SELECT strName FROM dbo.UserGroup WHERE ID=" + UserGroup_ID).ToString();
string UserGroupVerify = "OK";
switch (userType)
{
case "index": if (UserGroup_ID == 1 || UserGroup_ID == 2)
//case "index": if (UserGroup_ID == 1)
{
UserGroupVerify = "不存在的登陆账号!";
}
break;
case "adminindex": if (UserGroup_ID != 2 && UserGroup_ID != 3)
{
UserGroupVerify = "不存在的登陆账号!";
}
break;
case "sysadminindex": if (UserGroup_ID != 1)
{
UserGroupVerify = "不存在的登陆账号!";
}
break;
}
if (UserGroupVerify != "OK")
{
context.Response.Write(UserGroupVerify);
return;
}
////ip地址验证
//if (Convert.ToBoolean(common.GetWBAuthority()["ISWBControl"]) == true)//检验是否开启网点个数验证
//{
// if (UserGroupName == "营业员" || UserGroupName == "网点管理员")
// {
// //验证当前用户所在网点的ip地址是否可用
// object objIPaddress = SQLHelper.ExecuteScalar(" SELECT top 1 IPaddress FROM dbo.WB WHERE ID=" + dtAccount.Rows[0]["WB_ID"].ToString());
// if (objIPaddress == null || objIPaddress.ToString().Trim() == "")
// {
// UserGroupVerify = "!";
// context.Response.Write("系统没有为当前账户的网点配置可用的IP地址，请与系统管理员联系取得可用的IP地址!");
// return;
// }
// string ip = context.Request.QueryString["ip"].ToString();
// if (objIPaddress.ToString().Trim() != ip)
// {
// UserGroupVerify = "";
// context.Response.Write("当前计算机使用的IP地址无效，请与系统管理员联系取得可用的IP地址!");
// return;
// }
// }
//}
bool ErrorLoginCheck = false;//是否启用了秘密错误检验
if (UserGroup_ID == 3 || UserGroup_ID == 4)
{
if (Convert.ToBoolean(common.GetWBAuthority()["ErrorLogin_User"]) == true)
{
ErrorLoginCheck = true;
}
}
else
{
if (Convert.ToBoolean(common.GetWBAuthority()["ErrorLogin_Admin"]) == true)
{
ErrorLoginCheck = true;
}
}
string UserID = dtAccount.Rows[0]["ID"].ToString();
object objErrorTime = null;
if (ErrorLoginCheck)
{
objErrorTime = SQLHelper.ExecuteScalar(" SELECT TOP 1 ErrorTime FROM dbo.UserOperate WHERE UserID=" + UserID + " ORDER BY dt_LoginIn desc");
if (objErrorTime != null && objErrorTime.ToString() != "")
{
if (Convert.ToInt32(objErrorTime) >= 3)
{
DateTime dt_LoginIn = Convert.ToDateTime(SQLHelper.ExecuteScalar(" SELECT TOP 1 dt_LoginIn FROM dbo.UserOperate WHERE UserID=" + UserID + " ORDER BY dt_LoginIn desc"));
TimeSpan tsLogin = DateTime.Now.Subtract(dt_LoginIn);
if (tsLogin.TotalHours < 24)
{
Fun.Alert("您的密码已经连续三次输入错误，请于24小时后重试，或请求管理员解除限制！");
return;
}
}
}
}
string strPassword = dtAccount.Rows[0]["strPassword"].ToString();
bool ISEnable = Convert.ToBoolean(dtAccount.Rows[0]["ISEnable"]);
if (Fun.GetMD5_32(Password.Trim()) == strPassword)
//if (Fun.GetMD5_32(Password.Trim()) == strPassword || Password.Trim()==strPassword)//密码或价格后的信息
{
if (ISEnable)
{
if (HttpContext.Current.Session != null)
{
HttpContext.Current.Session.Clear();
}
DataTable dtwb = SQLHelper.ExecuteDataTable(" select * from WB where ID=" + dtAccount.Rows[0]["WB_ID"].ToString());
context.Session["WB_ID"] = dtAccount.Rows[0]["WB_ID"].ToString(); //该用户所在的网点ID
context.Session["ISHQ"] = dtwb.Rows[0]["ISHQ"].ToString(); //是否是总部网点
context.Session["ISSimulate"] = dtwb.Rows[0]["ISSimulate"].ToString(); //是否是模拟网点
context.Session["UserGroup_ID"] = UserGroup_ID;
context.Session["UserGroup_Name"] = UserGroupName;
context.Session["ID"] = dtAccount.Rows[0]["ID"].ToString();//用户ID
context.Session["strLoginName"] = dtAccount.Rows[0]["strLoginName"].ToString();//用户登录名
context.Session["strRealName"] = dtAccount.Rows[0]["strRealName"].ToString();
//设置保存session的Cookies值
context.Request.Cookies.Clear();
context.Response.Cookies.Clear();
HttpCookie cookie = new HttpCookie("LoginInfo");
DateTime dtNow = DateTime.Now;
TimeSpan ts = new TimeSpan(1, 0, 0, 0);//设置cookie的保存时间为一天
cookie.Expires = dtNow.Add(ts);
cookie.Values.Add("ID", dtAccount.Rows[0]["ID"].ToString());
HttpContext.Current.Response.Cookies.Add(cookie);
//添加营业员访问记录
AddUserOperate(false, dtAccount, context);
string userinfo = JsonHelper.ToJson(dtAccount);
//userinfo = userinfo.Substring(1);
//userinfo = userinfo.Substring(0, userinfo.Length - 1);
string wbinfo = JsonHelper.ToJson(dtwb);
//wbinfo = wbinfo.Substring(1);
//wbinfo = wbinfo.Substring(0, userinfo.Length - 1);
var returnValue = "{\"wbinfo\":" + wbinfo + ",\"userinfo\":" + userinfo + "}";
//context.Response.Write("Success");
context.Response.Write(returnValue);
return;
}
else
{
context.Response.Write("当前账户已被禁用，请与管理员联系!");
return;
}
}
else
{
AddUserOperate(true, dtAccount, context);
if (ErrorLoginCheck)
{
int numErrorTime = 0;
if (objErrorTime != null && objErrorTime.ToString() != "")
{
numErrorTime = Convert.ToInt32(objErrorTime) + 1;
}
context.Response.Write("这是您第" + numErrorTime + "次输入密码错误，连续输入错误3次以上您的账号将被禁用24小时！");
return;
}
else
{
context.Response.Write("您输入的密码不正确");
return;
}
}
}
else
{
context.Response.Write("不存在的登陆账号!");
return;
}
}