同时向主表和从表里面导入execl数据 （asp.net webform）

标签：流程   hrd   清空   仓库   btn   ret   编号   mon   小结   

  1  
  2 
  3 //前台
  4 
  5 
  6 
  7 
  8 //批量导入销售发货单
  9 protected void BtnExecl_Click(object sender, EventArgs e)
 10 {
 11 if (UPExecl.HasFile == false)//HasFile用来检查FileUpload是否有指定文件
 12 {
 13 Response.Write(" ");
 14 return;//当无文件时,返回
 15 }
 16 string IsXls = System.IO.Path.GetExtension(UPExecl.FileName).ToString().ToLower();//System.IO.Path.GetExtension获得文件的扩展名
 17 if (IsXls != ".xls" && IsXls != ".xlsm" && IsXls != ".xlsx")
 18 {
 19 Response.Write("");
 20 return;//当选择的不是Excel文件时,返回
 21 }
 22 string filename = UPExecl.FileName; //获取Execle文件名 DateTime日期函数
 23 string savePath = Server.MapPath(("~\\Files\\") + filename);//Server.MapPath 获得虚拟服务器相对路径
 24 UPExecl.SaveAs(savePath); //SaveAs 将上传的文件内容保存在服务器上
 25 DataSet ds = ExcelSqlConnection(savePath, filename);
 26 string resultmsg = ""; //结果信息
 27 //保存执行的sql
 28 ArrayList sqllist = new ArrayList();
 29 ///临时datatable
 30 DataTable shrinfo = new DataTable();
 31 int i = 0;
 32 string sql = "";
 33 DataSet rs = (DataSet)Session["e_xsfhd"];//销售发货单
 34 DataTable dt = null;
 35 if (ds == null || ds.Tables[0] == null || ds.Tables[0].Rows.Count == 0)
 36 {
 37 Response.Write(""); //当Excel表为空时,对用户进行提示
 38 }
 39 else
 40 {
 41 try
 42 {
 43 shrinfo = ds.Tables[0].Copy();//复制表结构
 44 shrinfo.Clear();//清空数据
 45 //循环读取execl表格里面的类容
 46 foreach (DataRow dr in ds.Tables[0].Rows)
 47 {
 48 sqllist.Clear();//清空错误信息
 49 string dhdw = dr["订货单位（必填）"].ToString();
 50 string ckmc = dr["发货仓库（必填）"].ToString();
 51 string ysgsbh = dr["运输公司（必填）"].ToString();
 52 string shr = dr["收货人（必填）"].ToString();
 53 string dhhm = dr["联系电话（必填）"].ToString();
 54 string szcs = dr["所在城市（必填）"].ToString();
 55 string sjrdz = dr["收件地址（必填）"].ToString();
 56 string mjID = dr["买家ID（必填）"].ToString();
 57 string xsr = dr["销售人"].ToString();
 58 string bz = dr["备注"].ToString();
 59 DataRow[] db2 = shrinfo.Select("收货人（必填）=‘" + shr + "‘ and 联系电话（必填）=‘" + dhhm + "‘ and 所在城市（必填）=‘" + szcs + "‘ and 收件地址（必填）=‘" + sjrdz + "‘ and 订货单位（必填）=‘" + dhdw + "‘ and 发货仓库（必填）=‘" + ckmc + "‘ and 运输公司（必填）=‘" + ysgsbh + "‘ and 买家ID（必填）=‘" + mjID + "‘");
 60 //判断当前收件人信息是否已经生成订单
 61 if (db2.Length == 0)
 62 {
 63 //对execl里面的数据行做一些简单的判断
 64 if (ckmc.Length == 0)
 65 {
 66 resultmsg += "收货人" + shr + "对应发货仓库有空，请检查之后再导入！\\n";
 67 continue;
 68 }
 69 if (ysgsbh.Length == 0)
 70 {
 71 resultmsg += "收货人" + shr + "对应运输公司有空，请检查之后再导入！\\n";
 72 continue;
 73 }
 74 if (shr.Length == 0)
 75 {
 76 resultmsg += "收货人信息有空，请检查之后再导入！\\n";
 77 continue;
 78 }
 79 if (dhhm.Length  0)
202 {
203 hgpkfpsl = int.Parse(dt.Rows[0]["hgpkfpsl"].ToString());
204 ss += sl;
205 if (ss > hgpkfpsl)
206 {
207 detailMsg += "收货人:" + shr + "名下货品名称" + hpmc + "对应的合格品可分配数量在库存中不足,请联系商务维护。\\n";
208 continue;
209 }
210 }
211 //判断该商品在仓库中的货架是否存在
212 sql = @"select hjdm from tb_i_hphj where ckdm=‘" + ckdm.Trim() + "‘ and hpdm=‘" + hpdm.Trim() + "‘ ";
213 dt = IFACE.DBUtility.DbHelperSQL.QueryDT(sql);
214 if (dt.Rows.Count > 0)
215 {
216 hj = dt.Rows[0]["hjdm"].ToString();
217 }
218 else
219 {
220 detailMsg += "收货人:" + shr + "名下货品名称" + hpmc + "对应的货架" + hj + "在系统中不存在,请联系商务或管理员维护。\\n";
221 continue;
222 }
223 //查询商品的成本价，批发价
224 //定义销售成本价的变量和批发价格的变量
225 decimal stockPrice = 0;
226 decimal price = 0;
227 //销售成本价
228 sql = "select stockprice from tb_p_stockprice where hpdm=‘" + hpdm.Trim() + "‘ and ckdm=‘" + ckdm.Trim() + "‘";
229 dt = IFACE.DBUtility.DbHelperSQL.QueryDT(sql);
230 if (dt.Rows.Count == 0)
231 {
232 detailMsg += "收货人:" + shr + "名下货品名称" + hpmc + "对应的成本价" + stockPrice + "在系统中不存在,请联系商务或管理员维护。\\n";
233 continue;
234 }
235 else
236 {
237 stockPrice = decimal.Parse(dt.Rows[0]["stockPrice"].ToString());
238 }
239 //批发价格
240 sql = "select price from tb_P_channelprice where hpdm=‘" + hpdm.Trim() + "‘ and khdj=‘" + khdj + "‘";
241 dt = IFACE.DBUtility.DbHelperSQL.QueryDT(sql);
242 if (dt.Rows.Count == 0)
243 {
244 detailMsg += "收货人:" + shr + "名下货品名称" + hpmc + "对应的批发价价" + price + "在系统中不存在,请联商务或管理员维护。\\n";
245 continue;
246 }
247 else
248 {
249 price = decimal.Parse(dt.Rows[0]["price"].ToString());
250 }
251 totalMoney += sl * hkze;//得到金额小结
252 orderTotalMoney = orderTotalMoney + sl * hkze;
253 //加入到销售单明细表
254 sql = @"insert into tb_s_xsfhmxb(djbh, xh, hpdm, sh, stockjg, channeljg, jg, sl, hj, sfsl, jexj, sfjyf, flcs, bjts, cbjg, jgdh, xscj, jgf, CreateDate)
255 values(‘" + djbh + "‘,‘" + xh + "‘,‘" + hpdm + "‘,‘" + 2012 + "‘,‘" + stockPrice + "‘,‘" + price + "‘,‘" + item["价格（必填）"].ToString() + "‘,‘" + item["数量（必填）"].ToString() + "‘,‘" + hj + "‘,‘" + 0 + "‘,‘" + totalMoney + "‘,‘" + 0 + "‘,‘" + 0 + "‘,‘" + 0 + "‘,‘" + 0 + "‘,‘" + jgdh + "‘,0,null,getdate())";
256 sqllist.Add(sql.ToString());
257 xh++;
258 }
259 //销售单主表
260 sql = @"insert into tb_s_xsfhtzdzb (djbh,ddbh,fph,crklx,ysfs,yffd,fhdw,fhck,ghdw,dhdw,zdr,zdsj,hpzt,fplx,jsfs,zrje,hkze,jghs,ysje,sfxysm,bz1,updatetime,shdz,shr,shrdh,shrcs,xsry,fkje,buyer_nick)
261 values(‘" + djbh + "‘,‘" + ddbh + "‘,‘" + fph + "‘,‘" + crklx + "‘,‘" + ysgsdm + "‘,‘" + yffd + "‘,‘" + fhdw + "‘,‘" + ckdm + "‘,‘" + ghdw + "‘,‘" + khdm + "‘,‘" + Session["UserID"].ToString() + "‘,getdate(),‘" + 1 + "‘,‘" + 1 + "‘,‘" + 1 + "‘,‘" + 0 + "‘,‘" + orderTotalMoney + "‘,‘" + 0 + "‘,‘" + 0 + "‘,0,‘" + bz + "‘,getdate(),‘" + sjrdz + "‘,‘" + shr + "‘,‘" + dhhm + "‘,‘" + szcs + "‘,‘" + xsr + "‘,‘" + orderTotalMoney + "‘,‘" + mjID + "‘)";
262 sqllist.Insert(0, sql.ToString());
263 //某一行出现错误那么在后面的那些数据都不会执行
264 // string errmesage = "";
265 if (detailMsg.Length > 0)
266 {
267 resultmsg += detailMsg;
268 //订单生成成功，将收件人信息保存至临时表
269 DataRow drs = shrinfo.NewRow();
270 drs["收货人（必填）"] = shr;
271 drs["联系电话（必填）"] = dhhm;
272 drs["所在城市（必填）"] = szcs;
273 drs["收件地址（必填）"] = sjrdz;
274 drs["订货单位（必填）"] = dhdw;
275 drs["发货仓库（必填）"] = ckmc;
276 drs["运输公司（必填）"] = ysgsbh;
277 drs["买家ID（必填）"] = mjID;
278 shrinfo.Rows.Add(drs);
279 }
280 else
281 {
282 string ymbh = "3001";
283 string shjg = "IFAC";
284 //string ymbh2 = "3002";
285 string qmsx = "1";
286 string qmsx2 = "2";
287 string lcbh = "1";
288 string sfls = "0";
289 string djlx = "1";
290 //默认的是零售流程，审核页面1
291 sql = @"insert into tb_sys_shjgb(djbh,ymbh,qmsx,shjg,CreateDate) values(‘" + djbh + "‘,‘" + ymbh + "‘,‘" + qmsx + "‘,‘" + shjg + "‘,getdate())";
292 sqllist.Add(sql.ToString());
293 //默认的是零售流程，审核页面2
294 sql = @"insert into tb_sys_shjgb(djbh,ymbh,qmsx,shjg,CreateDate) values(‘" + djbh + "‘,‘" + ymbh + "‘,‘" + qmsx2 + "‘,‘" + shjg + "‘,getdate())";
295 sqllist.Add(sql.ToString());
296 sql = @"insert into tb_sys_lctz(djbh,lcbh,sfls,djlx) values(‘" + djbh + "‘,‘" + lcbh + "‘,‘" + sfls + "‘,‘" + djlx + "‘)";
297 sqllist.Add(sql.ToString());
298 IFACE.DBUtility.DbHelperSQL.ExecuteSqlTran(sqllist);
299 //订单生成成功，将收件人信息保存至临时表
300 DataRow drs = shrinfo.NewRow();
301 drs["收货人（必填）"] = shr;
302 drs["联系电话（必填）"] = dhhm;
303 drs["所在城市（必填）"] = szcs;
304 drs["收件地址（必填）"] = sjrdz;
305 drs["订货单位（必填）"] = dhdw;
306 drs["发货仓库（必填）"] = ckmc;
307 drs["运输公司（必填）"] = ysgsbh;
308 drs["买家ID（必填）"] = mjID;
309 shrinfo.Rows.Add(drs);
310 i++;
311 }
312 }
313 else
314 {
315 continue;
316 }
317 }
318 }
319 catch (Exception ex)
320 {
321 Response.Write("");
322 }
323 }
324 if (resultmsg.Length > 500)
325 {
326 resultmsg = resultmsg.Substring(0, 500) + "......";
327 }
328 Response.Write("");
329 }

同时向主表和从表里面导入execl数据 （asp.net webform）

标签：流程   hrd   清空   仓库   btn   ret   编号   mon   小结   

原文地址：https://www.cnblogs.com/ljs-13/p/12424934.html