private void readExcel(string filename)
{
try
{
string connstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Server.MapPath("~/upload/" + filename) + ";Extended Properties=\"Excel 8.0;HDR=NO;IMEX=1;\"";
using (OleDbConnection conn = new OleDbConnection(connstring))
{
conn.Open();
DataTable sheetsName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" }); //得到所有sheet的名字
//for(int k=0;k<sheetsName.Rows.Count;k++)
//{
// log.Info(sheetsName.Rows[k][2].ToString());
//}
string firstSheetName = sheetsName.Rows[0][2].ToString(); //得到第一个sheet的名字
string sql = string.Format("SELECT * FROM [{0}]", firstSheetName); //查询字符串
OleDbDataAdapter ada = new OleDbDataAdapter(sql, connstring);
DataSet set = new DataSet();
ada.Fill(set);
DataTable dt = set.Tables[0];
SQLHelper.DbHelperSQL.SetConnectionString("");
ArrayList al = new ArrayList();
for (int i = 1; i < dt.Rows.Count; i++)
{
if (dt.Rows[i][0].ToString() == "" && dt.Rows[i][1].ToString() == "" && dt.Rows[i][2].ToString() == "" && dt.Rows[i][3].ToString() == "" && dt.Rows[i][4].ToString() == "" && dt.Rows[i][5].ToString() == "" && dt.Rows[i][6].ToString() == "" && dt.Rows[i][7].ToString() == "" && dt.Rows[i][8].ToString() == "" && dt.Rows[i][9].ToString() == "")
{
break;
}
else
{
sql = "select count(*) from Provider where ProviderNo='" + dt.Rows[i][0].ToString() + "'";
if (int.Parse(SQLHelper.DbHelperSQL.GetSingle(sql, 30)) > 0)
{
sql = "update Provider set Rank='" + dt.Rows[i][2].ToString() + "',Stype='" + dt.Rows[i][3].ToString() + "',Address='" + dt.Rows[i][4].ToString() + "',ContactMan='" + dt.Rows[i][5].ToString() + "',Telephone='" + dt.Rows[i][6].ToString() + "',Fax='" + dt.Rows[i][7].ToString() + "',Email='" + dt.Rows[i][8].ToString() + "',PurchaseMan='" + dt.Rows[i][9].ToString() + "',IsValid='" + dt.Rows[i][10].ToString() + "' where ProviderNo='" + dt.Rows[i][0].ToString() + "'";
al.Add(sql);
}
else
{
string spid = dt.Rows[i][0].ToString();
spid = spid.Substring(2);
spid = "SP" + spid.PadLeft(3, '0');
sql = "insert into Provider(ProviderNo,name,Rank,Stype,Address,ContactMan,Telephone,Fax,Email,PurchaseMan,IsValid) values('" + spid + "','" + dt.Rows[i][1].ToString() + "','" + dt.Rows[i][2].ToString() + "','" + dt.Rows[i][3].ToString() + "','" + dt.Rows[i][4].ToString() + "','" + dt.Rows[i][5].ToString() + "','" + dt.Rows[i][6].ToString() + "','" + dt.Rows[i][7].ToString() + "','" + dt.Rows[i][8].ToString() + "','" + dt.Rows[i][9].ToString() + "','" + dt.Rows[i][10].ToString() + "')";
al.Add(sql);
FileOper.writeLog(sql);
}
}
}
if (al.Count > 0 && SQLHelper.DbHelperSQL.ExecuteSqlTran(al))
{
Alert.Show("导入成功");
}
else
{
Alert.Show("导入失败");
}
}
}
catch (Exception ee)
{
log.Info(ee.ToString());
}
finally
{
BindGrid();
}
}
protected void btnPLSend_Click(object sender, EventArgs e)
{
try
{
if (Grid2.GetModifiedData().Count != 0 || Grid2.GetNewAddedList().Count != 0)
{
Alert.Show("表格还有未保存的数据，请先保存！");
return;
}
if (Grid2.SelectedRowIndexArray.Length == 0)
{
Alert.Show("请选择物料");
return;
}
StringBuilder sql = new StringBuilder();
ArrayList al = new ArrayList();
string s = "", orderno = "", prono = "", pname = "";
SQLHelper.DbHelperSQL.SetConnectionString("");
foreach (int i in Grid2.SelectedRowIndexArray)
{
orderno = Grid1.Rows[Grid1.SelectedRowIndex].Values[1].ToString();
prono = Grid1.Rows[Grid1.SelectedRowIndex].Values[3].ToString();
pname = Grid1.Rows[Grid1.SelectedRowIndex].Values[4].ToString();
s = "select count(*) from Instruction where OrderNo='" + orderno + "' and ProNo='" + prono + "' and itemno='" + Grid2.Rows[i].Values[2].ToString() + "' and IsConfirm=0";
if (int.Parse(SQLHelper.DbHelperSQL.GetSingle(s, 30)) > 0)
{
continue;
}
s = "select (case when sum(ConfirmQuantity) is null then 0 else sum(ConfirmQuantity) end) from instruction where orderno='" + orderno + "' and prono='" + prono + "' and itemno='" + Grid2.Rows[i].Values[2].ToString() + "'";
sql.Clear();
sql.Append("insert into Instruction(OrderNo,ProNo,ProName,ItemNo,ItemName,Spec,Material,SurfaceDeal,UsingQuantity,Sclass,MainFrom,Inputer,InputeDate,IsConfirm,IsPlan,ReceiveDept,Receiver,BarCode,BomSN)");
sql.Append(" values(");
sql.Append("'" + orderno + "',");
sql.Append("'" + prono + "',");
sql.Append("'" + pname + "',");
sql.Append("'" + Grid2.Rows[i].Values[2].ToString() + "',"); //ItemNo
sql.Append("'" + Grid2.Rows[i].Values[3].ToString() + "',"); //ItemName
sql.Append("'" + Grid2.Rows[i].Values[4].ToString() + "',"); //Spec
sql.Append("'" + Grid2.Rows[i].Values[5].ToString() + "',"); //Material
sql.Append("'" + Grid2.Rows[i].Values[6].ToString() + "',"); //SurfaceDeal
sql.Append("" + Grid2.Rows[i].Values[8].ToString() + "-(" + s + "),"); //UsingQuantity
sql.Append("'" + Grid2.Rows[i].Values[9].ToString() + "',"); //Sclass
sql.Append("'" + Grid2.Rows[i].Values[12].ToString() + "',"); //MainFrom
sql.Append("'" + User.Identity.Name + "',");
sql.Append("getdate(),");
sql.Append("0,");
sql.Append("0,");
sql.Append("'" + ddlDept.SelectedText + "',");
sql.Append("'" + ddlUser.SelectedValue + "',");
sql.Append("'" + DateTime.Now.ToString("yyyyMMddHHmmssffff") + "',");
sql.Append(Grid2.DataKeys[i][0]);
sql.Append(")");
al.Add(sql.ToString());
System.Threading.Thread.Sleep(1);
FileOper.writeLog(sql.ToString());
}
if (SQLHelper.DbHelperSQL.ExecuteSqlTran(al))
{
Alert.Show("发送成功");
BindGrid2();
}
else
{
Alert.Show("发送失败");
}
}
catch (Exception ee)
{
Alert.Show(ee.ToString());
}
}
private void readExcel(string filename)
{
string fsn = "";
try
{
string connstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Server.MapPath("~/OrderFile/" + filename) + ";Extended Properties=\"Excel 8.0;HDR=NO;IMEX=1;\"";
ArrayList al = new ArrayList();
using (OleDbConnection conn = new OleDbConnection(connstring))
{
conn.Open();
DataTable sheetsName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" }); //得到所有sheet的名字
//for(int k=0;k<sheetsName.Rows.Count;k++)
//{
// log.Info(sheetsName.Rows[k][2].ToString());
//}
string firstSheetName = sheetsName.Rows[0][2].ToString(); //得到第一个sheet的名字
string sql = string.Format("SELECT * FROM [{0}]", firstSheetName); //查询字符串
OleDbDataAdapter ada = new OleDbDataAdapter(sql, connstring);
DataSet set = new DataSet();
ada.Fill(set);
DataTable dt = set.Tables[0];
int i = 0;
SQLHelper.DbHelperSQL.SetConnectionString("");
string OrderNo = "", LotNo = "", ClientCode = "", ClientOrderNo = "", RecOrderPersonID = "", RecOrderPerson = "", RecOrderDate = "", SendOrderDate = "", OutGoodsDate = "", checkman = "", CheckDate = "", ContainerType = "", ischeck = "", OrderType = "";
//产品名称、产品编号、版本、瑞麟编号、客户编号、客户代号、日期
//获取表头 添加orderheader
if (dt != null && dt.Rows.Count >= 6)
{
OrderNo = dt.Rows[1][2].ToString();
if (OrderNo.Length == 12 && OrderNo.Substring(0, 2) == "RL" && CommFunction.IsNumeric(OrderNo.Substring(2, 10)))
{
}
else
{
Alert.Show("订单号格式不正确,禁止导入");
return;
}
OrderType = dt.Rows[1][5].ToString();
LotNo = dt.Rows[1][8].ToString();
ClientCode = dt.Rows[1][11].ToString();
ClientOrderNo = dt.Rows[2][2].ToString();
RecOrderPerson = dt.Rows[3][2].ToString();
checkman = dt.Rows[3][11].ToString();
ContainerType = dt.Rows[3][8].ToString();
RecOrderDate = dt.Rows[4][2].ToString();
CheckDate = dt.Rows[4][8].ToString();
OutGoodsDate = dt.Rows[4][11].ToString();
if (CheckDate != "" || checkman != "")
{
ischeck = "1";
}
else
{
ischeck = "0";
}
sql = "select sn from OrderHeader where OrderNo='" + OrderNo + "'";
log.Info(sql);
DataTable dtitem = SQLHelper.DbHelperSQL.ReturnDataTable(sql, 30);
if (dtitem == null || dtitem.Rows.Count == 0)
{
//sql = "insert into allitem(itemno,name) values('" + dt.Rows[2][4].ToString() + "','" + dt.Rows[2][2].ToString() + "')";
//log.Info("sqlallitem::::" + sql);
//al.Add(sql);
sql = "insert into OrderHeader(OrderNo,LotNo,ClientCode,ClientOrderNo,RecOrderPerson,RecOrderDate,SendOrderDate,OutGoodsDate,Inputer,InputerDate,IsCheck,OrderExcel,ContainerType,Checker,CheckDate,OrderType) values('" + OrderNo + "','" + LotNo + "','" + ClientCode + "','" + ClientOrderNo + "','" + RecOrderPerson + "','" + RecOrderDate.Replace(".", "-") + "','" + SendOrderDate.Replace(".", "-") + "','" + OutGoodsDate.Replace(".", "-") + "','" + User.Identity.Name + "',getdate()," + ischeck + ",'" + filename + "','" + ContainerType + "','" + checkman + "','" + CheckDate + "','" + OrderType + "')";
log.Info("sqlbase::::" + sql);
al.Add(sql);
//SQLHelper.DbHelperSQL.ExecuteSql(sql, 30);
}
else
{
Alert.Show("订单号已存在");
return;
//产品名称、产品编号、版本、瑞麟编号、客户编号、客户代号、日期
//sql = "update OrderHeader set LotNo='" + LotNo + "',ClientCode='" + ClientCode + "',ClientOrderNo='" + ClientOrderNo + "',RecOrderPerson='" + RecOrderPerson + "',RecOrderDate='" + RecOrderDate.Replace(".", "-") + "',SendOrderDate='" + SendOrderDate.Replace(".", "-") + "',OutGoodsDate='" + OutGoodsDate.Replace(".", "-") + "',Updater='" + User.Identity.Name + "',UpdateDate=getdate(),OrderExcel='" + filename + "',ContainerType='" + ContainerType + "',Checker='" + checkman + "',CheckDate='" + CheckDate + "',IsCheck="+ischeck+",OrderType='"+OrderType+"' where OrderNo='" + OrderNo + "'";
//log.Info("sqlbase::::" + sql);
//SQLHelper.DbHelperSQL.ExecuteSql(sql, 30);
}
//sql = "select max(sn) from OrderHeader where OrderNo='" + OrderNo + "'";
SQLHelper.DbHelperSQL.SetConnectionString("");
fsn = "select max(sn) from OrderHeader where OrderNo='" + OrderNo + "'";
#region orderdtl add
for (i = 6; i < dt.Rows.Count; i++)
{
if (dt.Rows[i][0].ToString() == "" || dt.Rows[i][1].ToString() == "")
{
break;
}
else
{
#region orderdtl add
if (dt.Rows[i][2].ToString() != "配件")
{
sql = "select count(*) from rlitems where itemno='" + dt.Rows[i][2].ToString() + "'";
if (int.Parse(SQLHelper.DbHelperSQL.GetSingle(sql, 30)) > 0)
{
//sql = "update rlitems set surfacedeal='" + dt.Rows[i][4].ToString() + "' where itemno='" + dt.Rows[i][2].ToString() + "'";
//al.Add(sql);
//FileOper.writeLog(sql);
}
else
{
sql = "insert into rlitems(itemno,itemname,surfacedeal,Unit,sclass) values('" + dt.Rows[i][2].ToString() + "','" + dt.Rows[i][1].ToString() + "','" + dt.Rows[i][4].ToString() + "','" + dt.Rows[i][6].ToString() + "','成品类')";
al.Add(sql);
FileOper.writeLog(sql);
}
}
else
{
sql = "insert into rlitems(itemno,itemname,surfacedeal,Unit,sclass) values('" + dt.Rows[i][2].ToString() + "','" + dt.Rows[i][1].ToString() + "','" + dt.Rows[i][4].ToString() + "','" + dt.Rows[i][6].ToString() + "','配件类')";
al.Add(sql);
FileOper.writeLog(sql);
}
sql = "insert into OrderDetail(FSN,OrderNo,ClinetNo,ItemNo,ItemName,Quantity,Demand1,Demand2,Remark,Inputer,InputerDate,Color,Unit,IsNew,IsPackingmaterials,CountryPackVer,IsChange) values((" + fsn + "),'" + OrderNo + "','" + dt.Rows[i][3].ToString() + "','" + dt.Rows[i][2].ToString() + "','" + dt.Rows[i][1].ToString() + "'," + dt.Rows[i][5].ToString() + ",'" + dt.Rows[i][11].ToString() + "','" + dt.Rows[i][12].ToString() + "','','" + User.Identity.Name + "',getdate(),'" + dt.Rows[i][4].ToString() + "','" + dt.Rows[i][6].ToString() + "','" + dt.Rows[i][7].ToString() + "','" + dt.Rows[i][8].ToString() + "','" + dt.Rows[i][9].ToString() + "','" + dt.Rows[i][10].ToString() + "')";
FileOper.writeLog(sql);
al.Add(sql);
//料号，名称，规格，材质，表面处理或颜色，底数，类别
//ItemNo,Name,Spec,MaterialNo,ItemColor,AddReserve1,ClassName
//sql = "select top 1 * from OrderDetail where itemno='" + dt.Rows[i][2].ToString() + "' and OrderNo='" + OrderNo + "' ";
//dtitem = SQLHelper.DbHelperSQL.ReturnDataTable(sql, 30);
//if (dtitem == null || dtitem.Rows.Count == 0)
//{
// //log.Info("sqlallitem::::" + sql);
// //SQLHelper.DbHelperSQL.ExecuteSql(sql, 30);
// //bomsn,物料sn，料号，名称，规格，材质，表面处理，用量，分类
// sql = "insert into OrderDetail(FSN,OrderNo,ClinetNo,ItemNo,ItemName,Quantity,Demand1,Demand2,Remark,Inputer,InputerDate,Color,Unit,IsNew,IsPackingmaterials,CountryPackVer,IsChange) values((" + fsn + "),'" + OrderNo + "','" + dt.Rows[i][3].ToString() + "','" + dt.Rows[i][2].ToString() + "','" + dt.Rows[i][1].ToString() + "'," + dt.Rows[i][5].ToString() + ",'" + dt.Rows[i][11].ToString() + "','" + dt.Rows[i][12].ToString() + "','','" + User.Identity.Name + "',getdate(),'" + dt.Rows[i][4].ToString() + "','" + dt.Rows[i][6].ToString() + "','" + dt.Rows[i][7].ToString() + "','" + dt.Rows[i][8].ToString() + "','" + dt.Rows[i][9].ToString() + "','" + dt.Rows[i][10].ToString() + "')";
// log.Info("sqldtl::::" + sql);
// al.Add(sql);
// //SQLHelper.DbHelperSQL.ExecuteSql(sql, 30);
//}
//else
//{
// sql = "update OrderDetail set ClinetNo='" + dt.Rows[i][3].ToString() + "',Quantity=" + dt.Rows[i][5].ToString() + ",Demand1='" + dt.Rows[i][11].ToString() + "',Demand2='" + dt.Rows[i][12].ToString() + "',Updater='" + User.Identity.Name + "',UpdateDate=getdate(),Color='" + dt.Rows[i][4].ToString() + "',Unit='" + dt.Rows[i][6].ToString() + "',IsNew='" + dt.Rows[i][7].ToString() + "',IsPackingmaterials='" + dt.Rows[i][8].ToString() + "',CountryPackVer='" + dt.Rows[i][9].ToString() + "',IsChange='" + dt.Rows[i][10].ToString() + "' where itemno='" + dt.Rows[i][1].ToString() + "' and ItemName='" + dt.Rows[i][2].ToString() + "' and OrderNo='" + OrderNo + "' ";
// log.Info("sqldtl::::" + sql);
// al.Add(sql);
// //SQLHelper.DbHelperSQL.ExecuteSql(sql, 30);
//}
#endregion
}
}
#endregion
}
else
{
Alert.Show("没有要导入的数据");
}
}
if (al.Count > 0 && SQLHelper.DbHelperSQL.ExecuteSqlTran(al))
{
Alert.Show("导入成功");
}
else
{
Alert.Show("导入失败");
}
}
catch (Exception ee)
{
Alert.Show("导入失败");
log.Info(ee.ToString());
}
finally
{
BindGrid();
}
}
private void readExcel(string filename)
{
try
{
string connstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Server.MapPath("~/upload/" + filename) + ";Extended Properties=\"Excel 8.0;HDR=NO;IMEX=1;\"";
using (OleDbConnection conn = new OleDbConnection(connstring))
{
conn.Open();
DataTable sheetsName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" }); //得到所有sheet的名字
//for(int k=0;k<sheetsName.Rows.Count;k++)
//{
// log.Info(sheetsName.Rows[k][2].ToString());
//}
string firstSheetName = sheetsName.Rows[0][2].ToString(); //得到第一个sheet的名字
string sql = string.Format("SELECT * FROM [{0}]", firstSheetName); //查询字符串
OleDbDataAdapter ada = new OleDbDataAdapter(sql, connstring);
DataSet set = new DataSet();
ada.Fill(set);
DataTable dt = set.Tables[0];
SQLHelper.DbHelperSQL.SetConnectionString("");
ArrayList al = new ArrayList();
for (int i = 1; i < dt.Rows.Count; i++)
{
if (dt.Rows[i][0].ToString() == "" && dt.Rows[i][1].ToString() == "" && dt.Rows[i][2].ToString() == "" && dt.Rows[i][3].ToString() == "" && dt.Rows[i][4].ToString() == "" && dt.Rows[i][5].ToString() == "" && dt.Rows[i][6].ToString() == "" && dt.Rows[i][7].ToString() == "" && dt.Rows[i][8].ToString() == "" && dt.Rows[i][9].ToString() == "")
{
break;
}
else
{
sql = "select count(*) from rlitems where itemno='" + dt.Rows[i][0].ToString() + "'";
if (int.Parse(SQLHelper.DbHelperSQL.GetSingle(sql, 30)) > 0)
{
//sql = "update rlitems set surfacedeal='" + dt.Rows[i][4].ToString() + "' where itemno='" + dt.Rows[i][0].ToString() + "'";
//al.Add(sql);
}
else
{
sql = "insert into rlitems(itemno,ItemName,spec,Material,surfacedeal,UnitWeight,Sclass,Unit,SupplierId,MainFrom) values('" + dt.Rows[i][0].ToString() + "','" + dt.Rows[i][1].ToString().Replace("'", "''") + "','" + dt.Rows[i][2].ToString() + "','" + dt.Rows[i][3].ToString() + "','" + dt.Rows[i][4].ToString() + "','" + dt.Rows[i][5].ToString() + "','" + dt.Rows[i][6].ToString() + "','" + dt.Rows[i][7].ToString() + "',(select sn from dbo.Provider where Name='" + dt.Rows[i][8].ToString() + "'),'" + dt.Rows[i][9].ToString() + "')";
al.Add(sql);
FileOper.writeLog(sql);
}
}
}
if (al.Count > 0 && SQLHelper.DbHelperSQL.ExecuteSqlTran(al))
{
Alert.Show("导入成功");
}
else
{
Alert.Show("导入失败");
}
}
}
catch (Exception ee)
{
log.Info(ee.ToString());
}
finally
{
BindGrid();
}
}
protected void btnSave_Click(object sender, EventArgs e)
{
try
{
if (Grid1.GetModifiedData().Count == 0 && Grid1.GetNewAddedList().Count == 0)
{
Alert.Show("表格数据没有变化！");
return;
}
ArrayList al = new ArrayList();
string sql = "", sqlval = "";
// 新增数据
List <Dictionary <string, object> > newAddedList = Grid1.GetNewAddedList();
//for (int i = 0; i < newAddedList.Count; i++)
//{
// sql = "insert into rlitems(itemno,itemname,Spec,Material,Price,SupplierId,UnitWeight,ProcessCost,ProcessCostType,SurfaceDeal,BaseNum,Sclass,WorkShop,MainFrom,StoreHouse,ProUsingQuantity,ZongCheng) values(";
// sql += "'" + newAddedList[i]["ItemNo"].ToString() + "',";
// sql += "'" + newAddedList[i]["ItemName"].ToString() + "',";
// sql += "'" + newAddedList[i]["Spec"].ToString() + "',";
// sql += "'" + newAddedList[i]["Material"].ToString() + "',";
// sql += "" + newAddedList[i]["Price"].ToString() + ",";
// sql += "" + newAddedList[i]["SupplierId"].ToString() + ",";
// sql += "" + newAddedList[i]["UnitWeight"].ToString() + ",";
// sql += "" + newAddedList[i]["ProcessCost"].ToString() + ",";
// sql += "'" + newAddedList[i]["ProcessCostType"].ToString() + "',";
// sql += "'" + newAddedList[i]["SurfaceDeal"].ToString() + "',";
// sql += "" + newAddedList[i]["BaseNum"].ToString() + ",";
// sql += "'" + newAddedList[i]["Sclass"].ToString() + "',";
// sql += "'" + newAddedList[i]["WorkShop"].ToString() + "',";
// sql += "'" + newAddedList[i]["MainFrom"].ToString() + "',";
// sql += "'" + newAddedList[i]["StoreHouse"].ToString() + "',";
// sql += "" + newAddedList[i]["ProUsingQuantity"].ToString() + ",";
// sql += "'" + newAddedList[i]["ZongCheng"].ToString() + "')";
// log.Info("sql add item:" + sql);
// al.Add(sql);
//}
for (int i = 0; i < newAddedList.Count; i++)
{
sql = "insert into rlitems(";
sqlval = " values(";
foreach (string key in newAddedList[i].Keys)
{
sql += key + ",";
sqlval += "'" + newAddedList[i][key].ToString() + "',";
}
sql = sql.TrimEnd(new char[] { ',' }) + ")";
sqlval = sqlval.TrimEnd(new char[] { ',' }) + ")";
sql = sql + sqlval;
al.Add(sql);
log.Info("sql item add:::" + sql);
}
//Alert.Show(sql);
//return;
//s = "";
// 修改的现有数
Dictionary <int, Dictionary <string, object> > modifiedDict = Grid1.GetModifiedDict();
SQLHelper.DbHelperSQL.SetConnectionString("");
foreach (int rowIndex in modifiedDict.Keys)
{
sql = "update rlitems set ";
for (int i = 0; i < Grid1.Columns.Count; i++)
{
if (modifiedDict[rowIndex].ContainsKey(Grid1.Columns[i].ColumnID))
{
sql += Grid1.Columns[i].ColumnID + "='" + modifiedDict[rowIndex][Grid1.Columns[i].ColumnID].ToString() + "',";
}
}
sql = sql.TrimEnd(new char[] { ',' });
sql += " where sn=" + Grid1.DataKeys[rowIndex][0];
FileOper.writeLog(sql);
al.Add(sql);
}
//al.Add(sql);
//sql = "update AllItem set ItemNo='" + modifiedDict[rowIndex]["ItemNo"].ToString() + "',Name='" + modifiedDict[rowIndex]["Name"].ToString() + "',Spec='" + modifiedDict[rowIndex]["Spec"].ToString() + "',MaterialNo='" + modifiedDict[rowIndex]["MaterialNo"].ToString() + "',ItemColor='" + modifiedDict[rowIndex]["ItemColor"].ToString() + "',AddReserve1='" + modifiedDict[rowIndex]["AddReserve1"].ToString() + "',ClassName='" + modifiedDict[rowIndex]["ClassName"].ToString() + "' where sn=" + GetSelectedDataKeyID(Grid1);
//Alert.Show(s);
SQLHelper.DbHelperSQL.SetConnectionString("");
if (SQLHelper.DbHelperSQL.ExecuteSqlTran(al))
{
Alert.Show("保存成功");
BindGrid();
}
else
{
Alert.Show("保存失败");
}
}
catch (Exception ee)
{
Alert.Show(ee.ToString());
}
}
private void BindGrid()
{
//string[] colstr = cols.ToString().Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
//string[] operstr=opers.ToString().Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
using (var appdb = new AppContext())
{
//IQueryable<Instruction> q = appdb.instruction;
var q = from a in appdb.instruction
join d in appdb.allitems on a.ItemNo equals d.ItemNo into d_join
from e in d_join.DefaultIfEmpty()
join b in appdb.v_userinfor on a.Receiver equals b.userid into userjoin
from c in userjoin.DefaultIfEmpty()
select new { a.SN, a.OrderNo, a.ProNo, a.ProName, a.ItemNo, a.ItemName, a.MainFrom, a.UsingQuantity, a.ConfirmQuantity, a.RealUsingQuantity, a.IsConfirm, a.Spec, a.Material, a.SurfaceDeal, a.Sclass, a.ReceiveDept, a.Receiver, c.ChineseName, a.ConfirmDate, a.BarCode, a.Remark, a.BomSN, a.IsPlan, e.SupplierId };
q = q.Where(u => u.RealUsingQuantity != 0 && u.IsConfirm == 1);
if (rbtmakethod.SelectedValue != "")
{
q = q.Where(u => u.MainFrom == rbtmakethod.SelectedValue);
}
if (rbtIsPlan.SelectedValue != "")
{
int k = int.Parse(rbtIsPlan.SelectedValue);
q = q.Where(u => u.IsPlan == k);
}
// 在职务名称中搜索
string searchText = ttbSearchMessage.Text.Trim();
if (!String.IsNullOrEmpty(searchText))
{
q = q.Where(t => t.ProName.Contains(searchText) || t.ItemName.Contains(searchText) || t.ProNo.Contains(searchText) || t.ItemNo.Contains(searchText));
}
foreach (DictionaryEntry de in htClickColsName)
{
switch (de.Key.ToString())
{
case "OrderNo":
q = q.Where(u => u.OrderNo == de.Value.ToString());
break;
case "ProNo":
q = q.Where(u => u.ProNo == de.Value.ToString());
break;
case "ProName":
q = q.Where(u => u.ProName == de.Value.ToString());
break;
case "ItemNo":
q = q.Where(u => u.ItemNo == de.Value.ToString());
break;
case "ItemName":
q = q.Where(u => u.ItemName == de.Value.ToString());
break;
case "Spec":
q = q.Where(u => u.Spec == de.Value.ToString());
break;
case "Material":
q = q.Where(u => u.Material == de.Value.ToString());
break;
case "SurfaceDeal":
q = q.Where(u => u.SurfaceDeal == de.Value.ToString());
break;
case "Sclass":
q = q.Where(u => u.Sclass == de.Value.ToString());
break;
case "MainFrom":
q = q.Where(u => u.MainFrom == de.Value.ToString());
break;
case "UsingQuantity":
q = q.Where(u => u.UsingQuantity.ToString() == de.Value.ToString());
break;
case "ReceiveDept":
q = q.Where(u => u.ReceiveDept == de.Value.ToString());
break;
case "Receiver":
q = q.Where(u => u.Receiver == de.Value.ToString());
break;
case "ConfirmDate":
q = q.Where(u => u.ConfirmDate.ToString() == de.Value.ToString());
break;
}
FileOper.writeLog("----------------------------------------------------------");
FileOper.writeLog("de.key::" + de.Key + ":::de.value::" + de.Value);
FileOper.writeLog("----------------------------------------------------------");
}
FileOper.writeLog("----------------------------------------------------------");
FileOper.writeLog(q.ToString());
FileOper.writeLog("----------------------------------------------------------");
// 在查询添加之后，排序和分页之前获取总记录数
Grid1.RecordCount = q.Count();
// 排列和分页
q = SortAndPage(q, Grid1);
Grid1.DataSource = q;
Grid1.DataBind();
}
}
private void BindGrid()
{
using (var appdb = new AppContext())
{
//IQueryable<ProducePlan> q = appdb.produceplan;
var qbom = from e in appdb.bombase
from f in appdb.bomdtl
where e.SN == f.FSN
select new { e.ProNo, e.ProName, SaleOrderNo = e.OrderNo, f.ItemNo, f.ItemName, f.IsValid, BomSN = e.SN };
if (rbtIsState.SelectedValue != "")
{
int k = int.Parse(rbtIsState.SelectedValue);
if (k == 0)
{
qbom = qbom.Where(u => u.IsValid == 0);
}
}
var q = from a in appdb.produceplan
//join b in appdb.allitems on a.ItemNo equals b.ItemNo into joinitem
//from c in joinitem.DefaultIfEmpty()
join d in qbom on new { a.ProNo, a.ItemNo, a.SaleOrderNo, a.BomSN } equals new { d.ProNo, d.ItemNo, d.SaleOrderNo, d.BomSN } into d_join
from g in d_join.DefaultIfEmpty()
select new { a.ItemNo, a.ItemName, a.ProName, a.ProNo, a.ProPlanNo, a.SaleOrderNo, a.Spec, a.Quantity, a.PlanStartDate, a.PlanFinishDate, a.WorkShop, a.SN, a.BomSN, a.State, g.IsValid, a.ZhuangPeiDate, a.ISN };
// 在职务名称中搜索
string searchText = ttbSearchMessage.Text.Trim();
if (!String.IsNullOrEmpty(searchText))
{
q = q.Where(t => t.ItemName.Contains(searchText) || t.ItemNo.Contains(searchText) || t.Spec.Contains(searchText));
}
if (rbtIsState.SelectedValue != "")
{
int k = int.Parse(rbtIsState.SelectedValue);
q = q.Where(u => u.State == k);
}
foreach (DictionaryEntry de in htClickColsName)
{
switch (de.Key.ToString())
{
case "OrderNo":
q = q.Where(u => u.SaleOrderNo == de.Value.ToString());
break;
case "ProNo":
q = q.Where(u => u.ProNo == de.Value.ToString());
break;
case "ProName":
q = q.Where(u => u.ProName == de.Value.ToString());
break;
case "ItemNo":
q = q.Where(u => u.ItemNo == de.Value.ToString());
break;
case "ItemName":
q = q.Where(u => u.ItemName == de.Value.ToString());
break;
case "Spec":
q = q.Where(u => u.Spec == de.Value.ToString());
break;
case "ProPlanNo":
q = q.Where(u => u.ProPlanNo == de.Value.ToString());
break;
case "SurfaceDeal":
q = q.Where(u => u.ProPlanNo == de.Value.ToString());
break;
case "Quantity":
q = q.Where(u => u.Quantity.ToString() == de.Value.ToString());
break;
case "WorkShop":
q = q.Where(u => u.WorkShop == de.Value.ToString());
break;
case "PlanFinishDate":
q = q.Where(u => u.PlanFinishDate.ToString() == de.Value.ToString());
break;
}
FileOper.writeLog("----------------------------------------------------------");
FileOper.writeLog("de.key::" + de.Key + ":::de.value::" + de.Value);
FileOper.writeLog("----------------------------------------------------------");
}
// 在查询添加之后，排序和分页之前获取总记录数
Grid1.RecordCount = q.Count();
// 排列和分页
q = SortAndPage(q, Grid1);
Grid1.DataSource = q;
Grid1.DataBind();
}
}
private void readExcel(string filename)
{
try
{
string connstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Server.MapPath("~/upload/" + filename) + ";Extended Properties=\"Excel 8.0;HDR=NO;IMEX=1;\"";
using (OleDbConnection conn = new OleDbConnection(connstring))
{
conn.Open();
DataTable sheetsName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" }); //得到所有sheet的名字
//for(int k=0;k<sheetsName.Rows.Count;k++)
//{
// log.Info(sheetsName.Rows[k][2].ToString());
//}
string firstSheetName = sheetsName.Rows[0][2].ToString(); //得到第一个sheet的名字
string sql = string.Format("SELECT * FROM [{0}]", firstSheetName); //查询字符串
OleDbDataAdapter ada = new OleDbDataAdapter(sql, connstring);
DataSet set = new DataSet();
ada.Fill(set);
DataTable dt = set.Tables[0];
string sn = GetQueryValue("sn");
string itemno = GetQueryValue("itemno");
SQLHelper.DbHelperSQL.SetConnectionString("");
ArrayList al = new ArrayList();
for (int i = 1; i < dt.Rows.Count; i++)
{
if (dt.Rows[i][0].ToString() == "" && dt.Rows[i][1].ToString() == "" && dt.Rows[i][2].ToString() == "" && dt.Rows[i][3].ToString() == "" && dt.Rows[i][4].ToString() == "" && dt.Rows[i][5].ToString() == "" && dt.Rows[i][6].ToString() == "" && dt.Rows[i][7].ToString() == "" && dt.Rows[i][8].ToString() == "" && dt.Rows[i][9].ToString() == "" && dt.Rows[i][10].ToString() == "" && dt.Rows[i][11].ToString() == "" && dt.Rows[i][12].ToString() == "")
{
break;
}
else
{
//sql = "select count(*) from ProcessRoute where ProcessCode='" + dt.Rows[i][0].ToString() + "'";
//if (int.Parse(SQLHelper.DbHelperSQL.GetSingle(sql, 30)) > 0)
//{
// //sql = "update rlitems set surfacedeal='" + dt.Rows[i][4].ToString() + "' where itemno='" + dt.Rows[i][0].ToString() + "'";
// //al.Add(sql);
//}
//else
//{
//}
sql = "insert into ProcessRoute (itemno,itemsn,ProcessingSeq,ProcessCode,ProcessName,MoldelNo,EquipmentNoName,Nature,Team,Department,WorkBatch,FixPerTime,ChangePerTime,Price,Remark) values('" + itemno + "'," + sn + ",'" + dt.Rows[i][0].ToString() + "','" + dt.Rows[i][1].ToString().Replace("'", "''") + "','" + dt.Rows[i][2].ToString() + "','" + dt.Rows[i][3].ToString() + "','" + dt.Rows[i][4].ToString() + "','" + dt.Rows[i][5].ToString() + "','" + dt.Rows[i][6].ToString() + "','" + dt.Rows[i][7].ToString() + "','" + dt.Rows[i][8].ToString() + "','" + dt.Rows[i][9].ToString() + "','" + dt.Rows[i][10].ToString() + "','" + dt.Rows[i][11].ToString() + "','" + dt.Rows[i][12].ToString() + "')";
al.Add(sql);
FileOper.writeLog(sql);
}
}
if (al.Count > 0 && SQLHelper.DbHelperSQL.ExecuteSqlTran(al))
{
Alert.Show("导入成功");
}
else
{
Alert.Show("导入失败");
}
}
}
catch (Exception ee)
{
log.Info(ee.ToString());
}
finally
{
BindGrid();
}
}
