/// <summary>
/// 执行多条SQL语句，实现数据库事务。
/// </summary>
/// <param name="SQLStringList">多条SQL语句</param>
public int ExecuteSqlTran(List <String> SQLStringList)
{
using (SqlConnection conn = new SqlConnection(GetDBPwd.GetDBConString_MSSQL(ConfigurationManager.AppSettings["AuthCode"].ToString(),
ConfigurationManager.AppSettings["UserName"].ToString(),
ConfigurationManager.AppSettings["DB"].ToString(),
ConfigurationManager.AppSettings["Env"].ToString())))
{
conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
SqlTransaction tx = conn.BeginTransaction();
cmd.Transaction = tx;
try
{
int count = 0;
for (int n = 0; n < SQLStringList.Count; n++)
{
string strsql = SQLStringList[n];
if (strsql.Trim().Length > 1)
{
cmd.CommandText = strsql;
int current = cmd.ExecuteNonQuery();
count += current == -1 ? 0 : current;
}
}
tx.Commit();
return(count);
}
catch (Exception)
{
tx.Rollback();
return(0);
}
}
}
//exec SP_Y_Inventory 'PD1111-2011-12-02-0001','aaaaaa',0
/// <summary>
/// 执行多条SQL语句，实现数据库事务。
/// </summary>
/// <param name="SQLStringList">多条SQL语句</param>
public string ExecuteProcdure(List <String> SQLStringList, out int count)
{
string result = "";
count = 0;
using (SqlConnection conn = new SqlConnection(GetDBPwd.GetDBConString_MSSQL(ConfigurationManager.AppSettings["AuthCode"].ToString(),
ConfigurationManager.AppSettings["UserName"].ToString(),
ConfigurationManager.AppSettings["DB"].ToString(),
ConfigurationManager.AppSettings["Env"].ToString())))
{
conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
try
{
for (int n = 0; n < SQLStringList.Count; n++)
{
string strsql = SQLStringList[n];
if (strsql.Trim().Length > 1)
{
cmd.CommandText = strsql;
string Temp = cmd.ExecuteScalar().ToString();
if (Temp != "0")
{
result += Temp + " 条码错误;<br />";
}
else
{
count++;
}
}
}
return(result);
}
catch (Exception)
{
return("");
}
finally
{
conn.Close();
}
}
}
protected void Report_DataBind(object sender, StoreRefreshDataEventArgs e)
{
if (Request["shopid"] == null)
{
return;
}
CJ_DevelopHelper.SqlConn_Str = GetDBPwd.GetDBConString_MSSQL(ConfigurationManager.AppSettings["AuthCode"].ToString(),
ConfigurationManager.AppSettings["UserName"].ToString(),
ConfigurationManager.AppSettings["DB"].ToString(),
ConfigurationManager.AppSettings["Env"].ToString());
CJ_DevelopHelper.SqlStr = "select depotid from j_user where userid='" + Request["shopid"].ToString() + "'";
DataTable dt = CJ_DevelopHelper.SQL_ReturnDateTable;
if (dt.Rows.Count <= 0)
{
return;
}
CJ_DevelopHelper.SqlStr = "select xl 系列,stycolorid 款式,get_date 上货日期,nums 销售,k_num 库存 from T_skctopsale_message where depotid1='" + dt.Rows[0][0].ToString() + "' and nums=0 order by xl desc";
Report_MainStore.DataSource = CJ_DevelopHelper.SQL_ReturnDateTable;
Report_MainStore.DataBind();
}
protected void Btnsave_Click(object sender, DirectEventArgs e)
{
try
{
string filename = UpFile.PostedFile.FileName;
string ext = Path.GetExtension(filename).ToLower();
if (ext != ".xls" && ext != ".xlsx")
{
X.Msg.Notify("失败", "<font color='red'>文件格式不正确，仅支持.xls(Excel)格式文件！</font>").Show();
return;
}
filename = DateTime.Now.ToString("yyyyMMddHHmmss") + ext;
UpFile.PostedFile.SaveAs(Server.MapPath("File/" + filename));
DataTable dt = new DataTable();
CJ_DevelopHelper.OtherCon_Str = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("File/" + filename) + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1;'";
CJ_DevelopHelper.SqlStr = "select 款式编号 as sku,色号 as color,F,F4,F5,F6,F7,F8,F9 from [sheet1$] where 款式编号<>''";
dt = CJ_DevelopHelper.Other_ReturnDateTable;
if (dt.Rows.Count <= 0)
{
X.Msg.Alert("提醒", "导入的文件无记录！").Show();
return;
}
string errorSKU = "";
CJ_DevelopHelper.SqlConn_Str = GetDBPwd.GetDBConString_MSSQL(ConfigurationManager.AppSettings["AuthCode"].ToString(),
ConfigurationManager.AppSettings["UserName"].ToString(),
ConfigurationManager.AppSettings["DB"].ToString(),
ConfigurationManager.AppSettings["Env"].ToString());
string allsku = "";
int rowcount = 0;
StringBuilder errtxt = new StringBuilder();
errtxt.AppendLine("款式编号\t色号\t尺码\t错因");
foreach (DataRow dr in dt.Rows)
{
string sku = dr[0].ToString().ToUpper();
if (sku.Length <= 0)
{
continue;
}
for (int i = 2; i <= 8; i++)
{
string size = i == 2 ? "F" : (i - 1).ToString();
if (dr[i].ToString().Trim().Length > 0)
{
if (int.Parse(dr[i].ToString()) < 0)
{
errorSKU += sku + dr[1].ToString().Trim() + size + " 数量为负;<br />";
errtxt.AppendLine(sku + "\t" + dr[1].ToString().Trim() + "\t" + size + "\t数量为负");
}
else
{
allsku += "'" + sku + dr[1].ToString().Trim() + size + "',";
rowcount++;
}
}
}
}
CJ_DevelopHelper.SqlStr = "select clothingid,styleid,colorid from j_clothing where clothingid in (" + allsku.TrimEnd(',') + ")";
DataTable dtDB = CJ_DevelopHelper.SQL_ReturnDateTable;
//找到错款编号
if (rowcount != dtDB.Rows.Count)
{
string [] sku = allsku.TrimEnd(',').Split(',');
for (int i = 0; i < sku.Length; i++)
{
int isfind = 0;
foreach (DataRow dr in dt.Rows)
{
if (dr[0].ToString().ToUpper() == sku[i].ToUpper().Trim('\''))
{
isfind = 1; break;
}
}
if (isfind == 0)
{
errorSKU += sku[i].Trim('\'') + " 款式错误;<br />";
errtxt.AppendLine(sku[i].Trim('\'').Substring(0, sku[i].Trim('\'').Length - 2)
+ "\t" + sku[i].Trim('\'').Substring(sku[i].Trim('\'').Length - 2, 1)
+ "\t" + sku[i].Trim('\'').Substring(sku[i].Trim('\'').Length - 1, 1)
+ "\t该款式无此尺码");
}
}
}
if (errorSKU != "")
{
string str = DateTime.Now.ToString("yyyyMMddHHmmss") + "Back_ERROR" + ".txt";
CJ_DevelopHelper.FilePath = Server.MapPath("File/") + str;
CJ_DevelopHelper.FileContent = errtxt.ToString();
if (!CJ_DevelopHelper.WriteFile)
{
X.Msg.Alert("提醒", "数据部分存在错误！<br />但生成异常文件时错误,请重试！").Show();
return;
}
X.Msg.Alert("提醒", "存在错误记录，请先检查！<br />请下载异常款文件<a href='File/" + str + "' target='_blank'>" + str + "</a>，修改后重新提交<br />错误提示：<br />" + errorSKU).Show();
return;
}
checknum_MainStore.DataSource = dt;
checknum_MainStore.DataBind();
if (File.Exists(Server.MapPath("File/" + filename)))
{
File.Delete(Server.MapPath("File/" + filename));
}
}
catch (Exception error)
{
X.Msg.Alert("错误", error.Message + "！请认真检查文档格式<br />各个款式退货数量是否存在非数字").Show();
}
}
private void LongAction(object state)
{
try
{
DataTable dt = (DataTable)Session["mainDT"];
string errorSKU = "";
CJ_DevelopHelper.SqlConn_Str = GetDBPwd.GetDBConString_MSSQL(ConfigurationManager.AppSettings["AuthCode"].ToString(),
ConfigurationManager.AppSettings["UserName"].ToString(),
ConfigurationManager.AppSettings["DB"].ToString(),
ConfigurationManager.AppSettings["Env"].ToString());
int totalnum = 0;
StringBuilder errtxt = new StringBuilder();
errtxt.AppendLine("条码\t数量");
int i = 1;//记录
List <string> sqlist = new List <string>();
this.Session["Total"] = dt.Rows.Count;
int successDataCount = 0;
foreach (DataRow dr in dt.Rows)
{
string sku = dr[0].ToString().ToUpper();
if (sku.Length <= 0)
{
continue;
}
if (int.Parse(dr[1].ToString()) <= 0)
{
errorSKU += sku + " 盘点数量为负或零;<br />";
continue;
}
else
{
sqlist.Add("exec SP_Y_Inventory '" + Session["listno"].ToString() + "','" + sku + "'," + dr[1].ToString().Trim() + "");
if (i % 50 == 0)//每50条，提交到数据库执行
{
int tmpcount = 0;
string temp = ExecuteProcdure(sqlist, out tmpcount);
if (temp != "")
{
errorSKU += temp;
}
sqlist.Clear();
successDataCount += tmpcount;
this.Session["LongActionProgress"] = i;
}
}
i++;
totalnum += int.Parse(dr[1].ToString());
}
if (sqlist.Count > 0)
{
int tmpcount = 0;
string temp = ExecuteProcdure(sqlist, out tmpcount);
if (temp != "")
{
errorSKU += temp;
}
sqlist.Clear();
successDataCount += tmpcount;
this.Session["LongActionProgress"] = i;
}
sqlist.Add("exec F22_RefreshDj 'd_inventory','" + Session["listno"].ToString() + "' ;");
ExecuteSqlTran(sqlist);
if (errorSKU != "")
{
this.Session["result"] = "导入成功" + successDataCount.ToString() + "条！但部分数据导入失败,存在错误,错误数据请检查后重新提交<br />错误数据：<br />" + errorSKU;
}
else
{
this.Session["result"] = "恭喜您，全部导入成功(" + successDataCount.ToString() + "条)！";
}
}
catch (Exception error)
{
this.Session["result"] = "异常：" + error.Message + "！请认真检查盘点文档格式";
}
finally
{
this.Session.Remove("mainDT");
this.Session.Remove("LongActionProgress");
this.Session.Remove("listno");
}
}