1 2 3 //前台 4 5 <asp:FileUpload ID="UPExecl" runat="server" /> 6 <asp:Button ID="BtnExecl" runat="server" Text="确认" OnClick="BtnExecl_Click" /> 7 8 //批量导入销售发货单 9 protected void BtnExecl_Click(object sender, EventArgs e) 10 { 11 if (UPExecl.HasFile == false)//HasFile用来检查FileUpload是否有指定文件 12 { 13 Response.Write("<script>alert(‘请您选择Excel文件‘)</script> "); 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("<script>alert(‘只可以选择Excel文件‘)</script>"); 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("<script>alert(‘Excel表为空表,无数据!‘)</script>"); //当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 < 11) 80 { 81 resultmsg += "收货人" + shr + "对应电话号码有不正确，请检查之后再导入！\\n"; 82 continue; 83 } 84 if (sjrdz.Length == 0) 85 { 86 resultmsg += "收货人" + shr + "对应地址有空，请检查之后再导入！\\n"; 87 continue; 88 } 89 if (mjID.Length == 0) 90 { 91 resultmsg += "收货人" + shr + "对应买家ID有空，请检查之后再导入！\\n"; 92 continue; 93 } 94 //对execl里面的一些必要字段做一些对比验证，看是否存在 95 //验证发货仓库在系统中是否存在（仓库代码） 96 string ckdm = ""; 97 dt = IFACE.DBUtility.DbHelperSQL.QueryDT("select ckdm from tb_m_ckdm where ckmc=‘" + ckmc.Trim() + "‘ and ckzt=1"); 98 if (dt.Rows.Count == 0) 99 {100 resultmsg += "收货人" + shr + "对应发货仓库：" + ckmc + "在系统中不存在！";101 continue;102 }103 else104 {105 ckdm = dt.Rows[0]["ckdm"].ToString();106 }107 //验证运输公司的代码是否存在108 string ysgsdm = "";109 sql = "select ysgsdm from dbo.tb_m_ysgsdm where ysgsmc=‘" + ysgsbh.Trim() + "‘";110 dt = IFACE.DBUtility.DbHelperSQL.QueryDT(sql);111 if (dt.Rows.Count == 0)112 {113 resultmsg += "收货人" + shr + "对应运输公司：" + ysgsbh + "在系统中不存在！";114 continue;115 }116 else117 {118 ysgsdm = dt.Rows[0]["ysgsdm"].ToString();119 }120 //验证订货单位是否存在即验证客户代码是否存在121 string khdm = "";122 string khdj = "";123 sql = "select khdm ,khdj from v_m_khdm where khmc=‘" + dhdw.Trim() + "‘";124 dt = IFACE.DBUtility.DbHelperSQL.QueryDT(sql);125 if (dt.Rows.Count == 0)126 {127 resultmsg += "收货人" + shr + "对应订货单位：" + dhdw + "在系统中不存在！";128 continue;129 }130 else131 {132 khdm = dt.Rows[0]["khdm"].ToString();133 khdj = dt.Rows[0]["khdj"].ToString();134 }135 //在生成销售单前需要做一些准备工作136 System.Data.SqlClient.SqlConnection Conn = new System.Data.SqlClient.SqlConnection(Application["connectionstring"].ToString());137 string djbh = DrpModule.RunID("tb_s_xsfhtzdzb", "djbh", "XSIFAC", Conn);138 string fhdw = "IFAC";139 string ghdw = "IFAC";//供货单位（默认IFAC）140 string ddbh = "";//订单编号141 string fph = "";//发票号142 string crklx = "1";143 string yffd = "1";144 decimal totalMoney = 0;//总金额145 decimal orderTotalMoney = 0;146 decimal hkze = 0;//价格147 int sl = 0;//数量148 //得到货品代码149 string hpdm = "";//150 string hj = "";151 //生成销售单 152 DataRow[] db = ds.Tables[0].Select("收货人（必填）=‘" + shr + "‘ and 联系电话（必填）=‘" + dhhm + "‘ and 所在城市（必填）=‘" + szcs + "‘ and 收件地址（必填）=‘" + sjrdz + "‘ and 订货单位（必填）=‘" + dhdw + "‘ and 发货仓库（必填）=‘" + ckmc + "‘ and 运输公司（必填）=‘" + ysgsbh + "‘ and 买家ID（必填）=‘" + mjID + "‘");153 string jgdh = "";154 int xh = 1;155 string detailMsg = "";156 string hpmc = "";157 foreach (DataRow item in db)158 {159 totalMoney = 0;//第二次循环清空第一次的数据160 //定义一个计算总金额的变量161 hpmc = item["货品名称（必填）"].ToString();162 sql = "select hpdm from tb_m_hpdm where hpmc=‘" + hpmc.Trim() + "‘";163 dt = IFACE.DBUtility.DbHelperSQL.QueryDT(sql);164 if (dt.Rows.Count == 0)165 {166 detailMsg += "收货人" + shr + "对应货品名称:" + hpmc + "在系统中不存在！";167 continue;168 }169 else170 {171 hpdm = dt.Rows[0]["hpdm"].ToString();172 }173 //验证数据174 try175 {176 sl = int.Parse(item["数量（必填）"].ToString());177 if (sl < 1)178 {179 detailMsg += "收货人" + shr + "对应货品名称" + hpmc + "对应的数量格式不能小于或者等于零。\\n";180 }181 }182 catch183 {184 detailMsg += "收货人" + shr + "对应货品名称" + hpmc + "对应的数量格式填写不正确。\\n";185 continue;186 }187 try188 {189 hkze = decimal.Parse(item["价格（必填）"].ToString());190 }191 catch192 {193 detailMsg += "收货人" + shr + "对应货品名称" + hpmc + "对应的价格格式填写不正确。\\n";194 continue;195 }196 //判断该商品是否有可分配数量197 int ss = 0;198 int hgpkfpsl = 0;199 sql = @"select hgpkfpsl from tb_i_dqkcjs where hpdm=‘" + hpdm + "‘ and ckdm=‘" + ckdm + "‘";200 dt = IFACE.DBUtility.DbHelperSQL.QueryDT(sql);201 if (dt.Rows.Count > 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 else219 {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 else236 {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 else248 {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 else281 {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 //默认的是零售流程，审核页面1291 sql = @"insert into tb_sys_shjgb(djbh,ymbh,qmsx,shjg,CreateDate) values(‘" + djbh + "‘,‘" + ymbh + "‘,‘" + qmsx + "‘,‘" + shjg + "‘,getdate())";292 sqllist.Add(sql.ToString());293 //默认的是零售流程，审核页面2294 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 else314 {315 continue;316 }317 }318 }319 catch (Exception ex)320 {321 Response.Write("<script>alert(‘" + ex.Message + "‘)</script>");322 }323 }324 if (resultmsg.Length > 500)325 {326 resultmsg = resultmsg.Substring(0, 500) + "......";327 }328 Response.Write("<script>alert(‘成功导入" + i + "条数据\\n" + resultmsg + "‘)</script>");329 }