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());
}
}
string printLabForHtmlByTable(string modelheadfile, string modelcontentfile)
{
string fname = DateTime.Now.ToString("yyyyMMddHHmmssffff") + ".html";
#region //获取模板文件内容
string strurlfile = HttpContext.Current.Server.MapPath("~/Model/" + modelheadfile + ".html");
StringBuilder htmltext = new StringBuilder(FileOper.getFileContent(strurlfile));
string sql = " select a.cporderno,a.remark,a.itemno,b.provider,a.itemname,a.spec,a.quantity,a.unit,c.material,c.surfacedeal,c.storehouse,BarCode from CPOrderDetail a join CPOrderHeader b on b.SN=a.FSN LEFT JOIN BomDetail c on a.bomsn = c.SN where a.fsn = " + Grid1.DataKeys[Grid1.SelectedRowIndex][0].ToString() + "";
SQLHelper.DbHelperSQL.SetConnectionString("");
DataTable dt = SQLHelper.DbHelperSQL.ReturnDataTable(sql, 30);
int pagecount = dt.Rows.Count;
htmltext.Replace("$pagecount", "张数：" + pagecount.ToString());
FileStream fs = new FileStream(HttpContext.Current.Server.MapPath("~/pdf/" + fname), FileMode.Append);
StreamWriter sw = new StreamWriter(fs);
sw.WriteLine(htmltext.ToString());
//获取模板内容
StringBuilder htmlcontent = new StringBuilder(FileOper.getFileContent(HttpContext.Current.Server.MapPath("~/Model/" + modelcontentfile + ".html")));
#endregion
try
{
#region 将模板内容循环添加PDF中
int pageindex = 1;
// 添加文档内容
for (int i = 0; i < pagecount; i++)
{
htmltext.Clear();
if (pageindex != 1)
{
htmltext.Append("<div class='page'><br></div>");
}
htmltext.Append(htmlcontent.ToString());
#region //替换模板内容
htmltext.Replace("$orderno", dt.Rows[i]["cporderno"].ToString());
htmltext.Replace("$sdate", DateTime.Now.ToString("yyyyMMdd"));
htmltext.Replace("$remark", dt.Rows[i]["remark"].ToString());
htmltext.Replace("$itemno", dt.Rows[i]["itemno"].ToString());
htmltext.Replace("$provider", dt.Rows[i]["provider"].ToString());
htmltext.Replace("$itemname", dt.Rows[i]["itemname"].ToString());
htmltext.Replace("$spec", dt.Rows[i]["spec"].ToString());
htmltext.Replace("$quantity", dt.Rows[i]["quantity"].ToString() + dt.Rows[i]["unit"].ToString());
htmltext.Replace("$material", dt.Rows[i]["material"].ToString());
htmltext.Replace("$color", dt.Rows[i]["surfacedeal"].ToString());
htmltext.Replace("$caseno", dt.Rows[i]["storehouse"].ToString());
htmltext.Replace("$codevalue", dt.Rows[i]["cporderno"].ToString());
//IDAutomation.NetAssembly.FontEncoder fe = new IDAutomation.NetAssembly.FontEncoder();
//htmltext.Replace("$code", fe.Code128(dt.Rows[i]["cporderno"].ToString(), 0, false));
//fe = null;
System.Threading.Thread.Sleep(5);
System.Drawing.Bitmap bitmap = BarcodeHelper.Generate1(dt.Rows[i]["BarCode"].ToString(), 150, 150);// CreateQRCode(str, 200, 5);
string s = DateTime.Now.ToString("yyyyMMddHHmmssffff");
string codejpg = HttpContext.Current.Server.MapPath("~/pdf/" + s + ".bmp");
//image.Save(codejpg);
bitmap.Save(codejpg);
htmltext.Replace("$code", s);
#endregion
sw.WriteLine(htmltext);
pageindex++;
}
#endregion
sw.WriteLine("</body></html>");
}
catch (Exception ee)
{
Alert.Show(ee.ToString());
}
finally
{
sw.Close();
fs.Close();
}
return(fname);
}
string printForHtmlByGrid(string modelheadfile, string modelcontentfile)
{
string fname = DateTime.Now.ToString("yyyyMMddHHmmssffff") + ".html";
#region //获取模板文件内容
string strurlfile = HttpContext.Current.Server.MapPath("~/Model/" + modelheadfile + ".html");
StringBuilder htmltext = new StringBuilder(FileOper.getFileContent(strurlfile));
int pagecount = Grid2.SelectedRowIndexArray.Length;
int labcount = int.Parse(numLabCount2.Text);
htmltext.Replace("$pagecount", "张数：" + (pagecount * labcount).ToString());
FileStream fs = new FileStream(HttpContext.Current.Server.MapPath("~/pdf/" + fname), FileMode.Append);
StreamWriter sw = new StreamWriter(fs);
sw.WriteLine(htmltext.ToString());
//获取模板内容
StringBuilder htmlcontent = new StringBuilder(FileOper.getFileContent(HttpContext.Current.Server.MapPath("~/Model/" + modelcontentfile + ".html")));
#endregion
try
{
#region 将模板内容循环添加PDF中
SQLHelper.DbHelperSQL.SetConnectionString("");
string sql = "";
int pageindex = 1;
for (int k = 0; k < labcount; k++)
{
// 添加文档内容
foreach (int i in Grid2.SelectedRowIndexArray)
{
htmltext.Clear();
if (pageindex != 1)
{
htmltext.Append("<div class='page'><br></div>");
}
htmltext.Append(htmlcontent.ToString());
#region //替换模板内容
htmltext.Replace("$orderno", Grid2.Rows[i].Values[3].ToString());
htmltext.Replace("$proorderno", Grid2.Rows[i].Values[2].ToString());
htmltext.Replace("$proname", Grid2.Rows[i].Values[5].ToString());
htmltext.Replace("$prono", Grid2.Rows[i].Values[4].ToString());
htmltext.Replace("$itemno", Grid2.Rows[i].Values[6].ToString());
htmltext.Replace("$itemname", Grid2.Rows[i].Values[7].ToString());
htmltext.Replace("$spec", Grid2.Rows[i].Values[8].ToString());
sql = "select material from bomdetail c,bomheader d where c.fsn=d.sn and c.itemno='" + Grid2.Rows[i].Values[6].ToString() + "' and d.prono='" + Grid2.Rows[i].Values[4].ToString() + "' and d.orderno='" + Grid2.Rows[i].Values[3].ToString() + "'";
htmltext.Replace("$material", SQLHelper.DbHelperSQL.GetSingle(sql, 30));
sql = "select surfacedeal from bomdetail c,bomheader d where c.fsn=d.sn and c.itemno='" + Grid2.Rows[i].Values[6].ToString() + "' and d.prono='" + Grid2.Rows[i].Values[4].ToString() + "' and d.orderno='" + Grid2.Rows[i].Values[3].ToString() + "'";
htmltext.Replace("$codevalue", Grid2.Rows[i].Values[11].ToString());
//IDAutomation.NetAssembly.FontEncoder fe = new IDAutomation.NetAssembly.FontEncoder();
//htmltext.Replace("$code", fe.Code128(Grid2.Rows[i].Values[2].ToString(), 0, false));
//fe = null;
System.Threading.Thread.Sleep(5);
System.Drawing.Bitmap bitmap = BarcodeHelper.Generate1(Grid2.Rows[i].Values[11].ToString(), 150, 150);// CreateQRCode(str, 200, 5);
string s = DateTime.Now.ToString("yyyyMMddHHmmssffff");
string codejpg = HttpContext.Current.Server.MapPath("~/pdf/" + s + ".bmp");
//image.Save(codejpg);
bitmap.Save(codejpg);
htmltext.Replace("$code", s);
#endregion
sw.WriteLine(htmltext);
pageindex++;
}
}
#endregion
sw.WriteLine("</body></html>");
}
catch (Exception ee)
{
Alert.Show(ee.ToString());
}
finally
{
sw.Close();
fs.Close();
}
return(fname);
}
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());
}
}
string printOrderForHtmlBySQL(string printsetfile, string htmlheadfile, string htmlcontentfile, string htmlfootfile)
{
string fname = DateTime.Now.ToString("yyyyMMddHHmmssffff") + ".html";
#region //获取模板文件内容
StringBuilder printsettext = new StringBuilder(FileOper.getFileContent(HttpContext.Current.Server.MapPath("~/Model/" + printsetfile + ".html")));
//int pagecount = Grid2.SelectedRowIndexArray.Length;
//int labcount = int.Parse(numLabCount2.Text);
printsettext.Replace("$pagecount", "");
FileStream fs = new FileStream(HttpContext.Current.Server.MapPath("~/pdf/" + fname), FileMode.Append);
StreamWriter sw = new StreamWriter(fs);
sw.WriteLine(printsettext.ToString());
//获取模板内容
StringBuilder htmlheadtext = new StringBuilder(FileOper.getFileContent(HttpContext.Current.Server.MapPath("~/Model/" + htmlheadfile + ".html")));
StringBuilder htmlcontenttext = new StringBuilder(FileOper.getFileContent(HttpContext.Current.Server.MapPath("~/Model/" + htmlcontentfile + ".html")));
StringBuilder htmlfoottext = new StringBuilder(FileOper.getFileContent(HttpContext.Current.Server.MapPath("~/Model/" + htmlfootfile + ".html")));
#endregion
try
{
#region 将模板内容循环添加PDF中
SQLHelper.DbHelperSQL.SetConnectionString("");
int pageindex = 1;
//替换头部
string sql = "select * from CPOrderHeader where sn=" + Grid1.DataKeys[Grid1.SelectedRowIndex][0].ToString();
SQLHelper.DbHelperSQL.SetConnectionString("");
DataTable dt = SQLHelper.DbHelperSQL.ReturnDataTable(sql, 30);
if (dt != null && dt.Rows.Count > 0)
{
//IDAutomation.NetAssembly.FontEncoder fe = new IDAutomation.NetAssembly.FontEncoder();
//htmlheadtext.Replace("$code$", fe.Code128(dt.Rows[0]["CPOrderNo"].ToString(), 0, false));
//fe = null;
System.Threading.Thread.Sleep(5);
System.Drawing.Bitmap bitmap = BarcodeHelper.Generate1(dt.Rows[0]["CPOrderNo"].ToString(), 150, 150);// CreateQRCode(str, 200, 5);
string s = DateTime.Now.ToString("yyyyMMddHHmmssffff");
string codejpg = HttpContext.Current.Server.MapPath("~/pdf/" + s + ".bmp");
bitmap.Save(codejpg);
htmlheadtext.Replace("$code$", s);
htmlheadtext.Replace("$CPOrderNo$", dt.Rows[0]["CPOrderNo"].ToString());
htmlheadtext.Replace("$codevalue$", dt.Rows[0]["CPOrderNo"].ToString());
htmlheadtext.Replace("$CPDate$", DateTime.Parse(dt.Rows[0]["CPDate"].ToString()).ToString("yyyy-MM-dd"));
htmlheadtext.Replace("$Provider$", dt.Rows[0]["Provider"].ToString());
htmlheadtext.Replace("$JBRName$", dt.Rows[0]["JBRName"].ToString());
htmlheadtext.Replace("$ContactMan$", dt.Rows[0]["ContactMan"].ToString());
htmlheadtext.Replace("$Tel$", dt.Rows[0]["Tel"].ToString());
htmlheadtext.Replace("$Fax$", dt.Rows[0]["Fax"].ToString());
htmlfoottext.Replace("$JHDate$", DateTime.Parse(dt.Rows[0]["JHDate"].ToString()).ToString("yyyy-MM-dd"));
htmlfoottext.Replace("$JHPlace$", dt.Rows[0]["JHPlace"].ToString());
}
sw.WriteLine(htmlheadtext);
StringBuilder str = new StringBuilder();
string itemrows = "";
float sum = 0;
//for (int k = 0; k < labcount; k++)
//{
str.Clear();
if (pageindex != 1)
{
str.Append("<div class='page'><br></div>");
}
str.Append(htmlcontenttext.ToString());
sql = "select * from dbo.CPOrderDetail where fsn=" + dt.Rows[0]["SN"].ToString();
dt = SQLHelper.DbHelperSQL.ReturnDataTable(sql, 30);
// 添加文档内容
for (int i = 0; i < dt.Rows.Count; i++)
{
itemrows += "<tr><td class='b_b_r' style='padding:10px;'>" + (i + 1).ToString().PadLeft(3 - (i + 1).ToString().Length, '0') + "</td><td class='b_b_r'>" + dt.Rows[i]["itemno"].ToString() + "</td><td class='b_b_r'>" + dt.Rows[i]["itemname"].ToString() + "</td><td class='b_b_r'>" + dt.Rows[i]["spec"].ToString() + "</td><td class='b_b_r'>" + dt.Rows[i]["unit"].ToString() + "</td><td class='b_b_r'>" + dt.Rows[i]["quantity"].ToString() + "</td><td class='b_b'>" + dt.Rows[i]["remark"].ToString() + "</td></tr>";
sum += float.Parse(dt.Rows[i]["quantity"].ToString());
}
itemrows += "<tr><td class='b_r' style='padding:10px;' colspan='5'>合计：</td><td class='b_r'>" + sum.ToString() + "</td><td class=''></td></tr>";
str.Replace("$items", itemrows);
sw.WriteLine(str);
pageindex++;
//}
#endregion
sw.WriteLine(htmlfoottext.ToString());
sw.WriteLine(" </div></body></html>");
}
catch (Exception ee)
{
Alert.Show(ee.ToString());
}
finally
{
sw.Close();
fs.Close();
}
return(fname);
}
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();
}
}
string printOrderForHtmlBySQL(string printsetfile, string htmlheadfile, string headcontentfile, string htmlfootfile)
{
string fname = DateTime.Now.ToString("yyyyMMddHHmmssffff") + ".html";
#region //获取模板文件内容
StringBuilder printsettext = new StringBuilder(FileOper.getFileContent(HttpContext.Current.Server.MapPath("~/Model/" + printsetfile + ".html")));
//int pagecount = Grid2.SelectedRowIndexArray.Length;
//int labcount = int.Parse(numLabCount2.Text);
printsettext.Replace("$pagecount", "");
FileStream fs = new FileStream(HttpContext.Current.Server.MapPath("~/pdf/" + fname), FileMode.Append);
StreamWriter sw = new StreamWriter(fs);
sw.WriteLine(printsettext.ToString());
//获取模板内容
StringBuilder htmlheadtext = new StringBuilder(FileOper.getFileContent(HttpContext.Current.Server.MapPath("~/Model/" + htmlheadfile + ".html")));
StringBuilder headcontenttext = new StringBuilder(FileOper.getFileContent(HttpContext.Current.Server.MapPath("~/Model/" + headcontentfile + ".html")));
StringBuilder htmlfoottext = new StringBuilder(FileOper.getFileContent(HttpContext.Current.Server.MapPath("~/Model/" + htmlfootfile + ".html")));
#endregion
try
{
#region 将模板内容循环添加PDF中
SQLHelper.DbHelperSQL.SetConnectionString("");
int pageindex = 1;
//替换头部
string sql = "select * from produceorderheader where sn=" + Grid1.DataKeys[Grid1.SelectedRowIndex][0].ToString();
SQLHelper.DbHelperSQL.SetConnectionString("");
DataTable dt = SQLHelper.DbHelperSQL.ReturnDataTable(sql, 30);
if (dt != null && dt.Rows.Count > 0)
{
htmlheadtext.Replace("$ProOrderNo", dt.Rows[0]["ProOrderNo"].ToString());
htmlheadtext.Replace("$ProNo", dt.Rows[0]["ProNo"].ToString());
htmlheadtext.Replace("$InputeDate", dt.Rows[0]["InputeDate"].ToString());
htmlheadtext.Replace("$Quantity", dt.Rows[0]["Quantity"].ToString());
htmlheadtext.Replace("$Inputer", GetChineseName(dt.Rows[0]["Inputer"].ToString()));
htmlheadtext.Replace("$SaleOrderNo", dt.Rows[0]["SaleOrderNo"].ToString());
htmlheadtext.Replace("$Remark", dt.Rows[0]["Remark"].ToString());
}
sw.WriteLine(htmlheadtext);
StringBuilder str = new StringBuilder();
string itemrows = "";
sql = "select * from dbo.ProduceOrderDetail where fsn=" + dt.Rows[0]["SN"].ToString();
dt = SQLHelper.DbHelperSQL.ReturnDataTable(sql, 30);
//for (int k = 0; k < labcount; k++)
//{
str.Clear();
if (pageindex != 1)
{
str.Append("<div class='page'><br></div>");
}
str.Append(headcontenttext.ToString());
// 添加文档内容
for (int i = 0; i < dt.Rows.Count; i++)
{
if (i == dt.Rows.Count - 1)
{
itemrows += "<tr><td class='b_r' >" + dt.Rows[i]["itemno"].ToString() + "</td><td class='b_r'>" + dt.Rows[i]["itemname"].ToString() + "</td><td class='b_r'>" + dt.Rows[i]["spec"].ToString() + "</td><td class='b_r'></td><td class='b_r'></td><td class='b_r'>" + dt.Rows[i]["unit"].ToString() + "</td><td class='b_r td_c'>" + dt.Rows[i]["quantity"].ToString() + "</td><td class=''></td></tr>";
}
else
{
itemrows += "<tr><td class='b_b_r'>" + dt.Rows[i]["itemno"].ToString() + "</td><td class='b_b_r'>" + dt.Rows[i]["itemname"].ToString() + "</td><td class='b_b_r'>" + dt.Rows[i]["spec"].ToString() + "</td><td class='b_b_r'></td><td class='b_b_r'></td><td class='b_b_r'>" + dt.Rows[i]["unit"].ToString() + "</td><td class='b_b_r td_c'>" + dt.Rows[i]["quantity"].ToString() + "</td><td class='b_b'></td></tr>";
}
}
str.Replace("$items", itemrows);
sw.WriteLine(str);
pageindex++;
//}
#endregion
sw.WriteLine(htmlfoottext.ToString());
sw.WriteLine(" </div></body></html>");
}
catch (Exception ee)
{
Alert.Show(ee.ToString());
}
finally
{
sw.Close();
fs.Close();
}
return(fname);
}
string printOrderForHtmlBySQL(string printsetfile, string htmlheadfile, string htmlcontentfile, string htmlfootfile)
{
string fname = DateTime.Now.ToString("yyyyMMddHHmmssffff") + ".html";
#region //获取模板文件内容
StringBuilder printsettext = new StringBuilder(FileOper.getFileContent(HttpContext.Current.Server.MapPath("~/Model/" + printsetfile + ".html")));
//int pagecount = Grid2.SelectedRowIndexArray.Length;
//int labcount = int.Parse(numLabCount2.Text);
printsettext.Replace("$pagecount", "");
FileStream fs = new FileStream(HttpContext.Current.Server.MapPath("~/pdf/" + fname), FileMode.Append);
StreamWriter sw = new StreamWriter(fs);
sw.WriteLine(printsettext.ToString());
//获取模板内容
StringBuilder htmlheadtext = new StringBuilder(FileOper.getFileContent(HttpContext.Current.Server.MapPath("~/Model/" + htmlheadfile + ".html")));
StringBuilder htmlcontenttext = new StringBuilder(FileOper.getFileContent(HttpContext.Current.Server.MapPath("~/Model/" + htmlcontentfile + ".html")));
StringBuilder htmlfoottext = new StringBuilder(FileOper.getFileContent(HttpContext.Current.Server.MapPath("~/Model/" + htmlfootfile + ".html")));
#endregion
try
{
#region 将模板内容循环添加PDF中
SQLHelper.DbHelperSQL.SetConnectionString("");
int pageindex = 1;
//替换头部
string sql = "select * from SendOutProcessHeader where sn=" + Grid1.DataKeys[Grid1.SelectedRowIndex][0].ToString();
SQLHelper.DbHelperSQL.SetConnectionString("");
DataTable dt = SQLHelper.DbHelperSQL.ReturnDataTable(sql, 30);
if (dt != null && dt.Rows.Count > 0)
{
//IDAutomation.NetAssembly.FontEncoder fe = new IDAutomation.NetAssembly.FontEncoder();
//htmlheadtext.Replace("$code$", fe.Code128(dt.Rows[0]["CPOrderNo"].ToString(), 0, false));
//fe = null;
htmlheadtext.Replace("$SendOutOrderNo$", dt.Rows[0]["SendOutOrderNo"].ToString());
htmlheadtext.Replace("$SendOutDate$", DateTime.Parse(dt.Rows[0]["SendOutDate"].ToString()).ToString("yyyy年M月d日"));
htmlheadtext.Replace("$Provider$", dt.Rows[0]["Provider"].ToString());
htmlheadtext.Replace("$JBR$", dt.Rows[0]["JBRName"].ToString());
htmlheadtext.Replace("$Provideraddr$", SQLHelper.DbHelperSQL.GetSingle("select top 1 address from provider where sn='" + dt.Rows[0]["providerid"].ToString() + "' or name='" + dt.Rows[0]["provider"].ToString() + "'", 30));
htmlheadtext.Replace("$Checkcode$", "审核");
htmlheadtext.Replace("$Remark$", dt.Rows[0]["Remark"].ToString());
}
sw.WriteLine(htmlheadtext);
StringBuilder str = new StringBuilder();
string itemrows = "";
float sum = 0;
//for (int k = 0; k < labcount; k++)
//{
str.Clear();
if (pageindex != 1)
{
str.Append("<div class='page'><br></div>");
}
str.Append(htmlcontenttext.ToString());
sql = "select * from dbo.SendOutProcessDetail where fsn=" + dt.Rows[0]["SN"].ToString();
dt = SQLHelper.DbHelperSQL.ReturnDataTable(sql, 30);
// 添加文档内容
for (int i = 0; i < dt.Rows.Count; i++)
{
itemrows += "<tr><td class='b_b_r' style='padding:10px;'>" + (i + 1).ToString().PadLeft(3 - (i + 1).ToString().Length, '0') + "</td><td class='b_b_r'>" + dt.Rows[i]["itemno"].ToString() + "</td><td class='b_b_r'>" + dt.Rows[i]["itemname"].ToString() + "</td><td class='b_b_r'>" + dt.Rows[i]["spec"].ToString() + "</td><td class='b_b_r'>" + dt.Rows[i]["unit"].ToString() + "</td><td class='b_b_r'>" + dt.Rows[i]["quantity"].ToString() + "</td><td class='b_b_r'></td><td class='b_b'></td></tr>";
sum += float.Parse(dt.Rows[i]["quantity"].ToString());
}
itemrows += "<tr><td class='b_r' style='padding:10px;' colspan='5'>合计：</td><td class='b_r'>" + sum.ToString() + "</td><td class=''></td></tr>";
str.Replace("$items$", itemrows);
sw.WriteLine(str);
pageindex++;
//}
#endregion
sw.WriteLine(htmlfoottext.ToString());
sw.WriteLine(" </div></body></html>");
}
catch (Exception ee)
{
Alert.Show(ee.ToString());
}
finally
{
sw.Close();
fs.Close();
}
return(fname);
}
