public object GetGoodsTypeList(int pagnum, int pagesize, string goodstypename) { using (MySqlDbConnection dbc = MySqlConnstr.GetDBConnection()) { try { int cp = pagnum; int ac = 0; string where = ""; if (!string.IsNullOrEmpty(goodstypename.Trim())) { where += " and " + dbc.C_Like("a.name", goodstypename.Trim(), LikeStyle.LeftAndRightLike); } string str = @"select a.id goodsTypeId,a.name goodsTypeName,a.code goodsTypeCode,(select count(*) from tb_b_goods b where a.id=b.goodstype) goodsNum from tb_b_dictionary_detail a where a.bm like '008%'"; str += where; //开始取分页数据 System.Data.DataTable dtPage = new System.Data.DataTable(); dtPage = dbc.GetPagedDataTable(str + " order by a.bm asc", pagesize, ref cp, out ac); return(new { dt = dtPage, cp = cp, ac = ac }); } catch (Exception ex) { throw ex; } } }
public static SystemUser GetUserByID(string userid) { string sqlStr = "select a.UserID YH_ID, a.UserName YH_DLM,a.UserXM YH_XM,a.Password,b.roleId,b.companyId,'' MY_SQL_YH_ID from tb_b_user a left join tb_b_user_role b on a.UserID = b.userId where a.UserID = @yh_id"; SqlCommand cmd = new SqlCommand(sqlStr); cmd.Parameters.AddWithValue("@yh_id", userid); using (DBConnection dbc = new DBConnection()) { var dtb = dbc.ExecuteDataTable(cmd); if (dtb.Rows.Count == 0) { throw new Exception("无效的用户ID"); } using (MySqlDbConnection db = MySqlConnstr.GetDBConnection()) { sqlStr = "select userid from tb_b_user where correlationid=" + dbc.ToSqlValue(dtb.Rows[0]["YH_ID"].ToString()); DataTable mySqlqDt = db.ExecuteDataTable(sqlStr); if (mySqlqDt.Rows.Count > 0) { dtb.Rows[0]["MY_SQL_YH_ID"] = mySqlqDt.Rows[0]["userid"].ToString(); } } SystemUser su = new SystemUser(); su.m_data = dtb.Rows[0]; return(su); } }
public static SystemUser Login(string username, string password) { using (DBConnection dbc = new DBConnection()) { string sqlStr = "select a.UserID YH_ID, a.UserName YH_DLM,a.UserXM YH_XM,a.Password,b.roleId,b.companyId,'' MY_SQL_YH_ID from tb_b_user a left join tb_b_user_role b on a.UserID = b.userId where a.UserName=@LoginName and a.Password=@Password and (a.ClientKind = 0 or a.ClientKind = 99)"; SqlCommand cmd = new SqlCommand(sqlStr); cmd.Parameters.AddWithValue("@LoginName", username); cmd.Parameters.AddWithValue("@Password", password); var dtUser = dbc.ExecuteDataTable(cmd); SystemUser su = new SystemUser(); if (dtUser.Rows.Count > 0) { using (MySqlDbConnection db = MySqlConnstr.GetDBConnection()) { sqlStr = "select userid from tb_b_user where correlationid=" + dbc.ToSqlValue(dtUser.Rows[0]["YH_ID"].ToString()); DataTable mySqlqDt = db.ExecuteDataTable(sqlStr); if (mySqlqDt.Rows.Count > 0) { dtUser.Rows[0]["MY_SQL_YH_ID"] = mySqlqDt.Rows[0]["userid"].ToString(); } } su.m_data = dtUser.Rows[0]; HttpContext.Current.Response.Cookies.Add(new HttpCookie("userid", dtUser.Rows[0]["YH_ID"].ToString()) { HttpOnly = true }); return(su); } return(null); } }
public bool UpdateConstituteOffer(string offerid, JSReader jsr) { using (MySqlDbConnection dbc = MySqlConnstr.GetDBConnection()) { try { decimal estimateautomoney = string.IsNullOrEmpty(jsr["estimateautomoney"].ToString()) ? 0 : Convert.ToDecimal(jsr["estimateautomoney"].ToString()); //预估自动计算金额 decimal estimatecompletemoney = string.IsNullOrEmpty(jsr["estimatecompletemoney"].ToString()) ? 0 : Convert.ToDecimal(jsr["estimatecompletemoney"].ToString()); //预估综合成本 decimal estimatetaxmoney = string.IsNullOrEmpty(jsr["estimatetaxmoney"].ToString()) ? 0 : Convert.ToDecimal(jsr["estimatetaxmoney"].ToString()); //预估税费成本 decimal estimatecostmoney = string.IsNullOrEmpty(jsr["estimatecostmoney"].ToString()) ? 0 : Convert.ToDecimal(jsr["estimatecostmoney"].ToString()); //预估资金成本 if (estimateautomoney != 0 && (estimateautomoney == estimatecompletemoney + estimatetaxmoney + estimatecostmoney)) { string sql = @"update tb_b_sourcegoodsinfo_offer set estimatecompletemoney=" + dbc.ToSqlValue(estimatecompletemoney) + ",estimatetaxmoney=" + dbc.ToSqlValue(estimatetaxmoney) + ",estimatecostmoney=" + dbc.ToSqlValue(estimatecostmoney) + @" where offerid=" + dbc.ToSqlValue(offerid); dbc.ExecuteNonQuery(sql); return(true); } else { throw new Exception("组成成分填写错误!"); } } catch (Exception ex) { throw ex; } } }
public DataTable GetGoodsPriceLine(string goodsId, string fromroute, string toroute, string statisticstype) { using (MySqlDbConnection dbc = MySqlConnstr.GetDBConnection()) { try { string where = ""; if (!string.IsNullOrEmpty(fromroute)) { where += " and " + dbc.C_EQ("fromroutecode", fromroute); } if (!string.IsNullOrEmpty(toroute)) { where += " and " + dbc.C_EQ("toroutecode", toroute); } if (!string.IsNullOrEmpty(statisticstype)) { where += " and " + dbc.C_EQ("statisticstype", statisticstype); } string sql = @"select id,CASE statisticstype WHEN 1 THEN '零担' WHEN 2 THEN '整车' ELSE '' END transporttype, fromroutecode,fromroutename,toroutecode,toroutename,price,pickprice,deliverprice,frompart,topart from tb_b_pricemodel where goodsid=" + dbc.ToSqlValue(goodsId) + " and status=0 " + where + " order by updatetime desc"; DataTable dt = dbc.ExecuteDataTable(sql); return(dt); } catch (Exception ex) { throw ex; } } }
public bool UpdateSourceGoods(string offerid, JSReader jsr) { using (MySqlDbConnection dbc = MySqlConnstr.GetDBConnection()) { try { DateTime ti = DateTime.Now; String estimatemoney = jsr["estimatemoney"].ToString(); String totalmonetaryamount = jsr["totalmonetaryamount"].ToString(); var dt = dbc.GetEmptyDataTable("tb_b_sourcegoodsinfo_offer"); var dtt = new SmartFramework4v2.Data.DataTableTracker(dt); var dr = dt.NewRow(); dr["offerid"] = offerid; if (!string.IsNullOrEmpty(estimatemoney)) { dr["estimatemoney"] = Convert.ToDecimal(estimatemoney); } if (!string.IsNullOrEmpty(totalmonetaryamount)) { dr["totalmonetaryamount"] = Convert.ToDecimal(totalmonetaryamount); } dr["updateuser"] = SystemUser.CurrentUser.UserID; dr["updatetime"] = ti; dt.Rows.Add(dr); dbc.UpdateTable(dt, dtt); return(true); } catch (Exception ex) { throw ex; } } }
public DataTable GetGoodsById(string goodsId) { using (MySqlDbConnection dbc = MySqlConnstr.GetDBConnection()) { string sqlStr = "select * from tb_b_goods where goodsid='" + goodsId + "'"; return(dbc.ExecuteDataTable(sqlStr)); } }
public DataTable GetGoodsType() { using (MySqlDbConnection dbc = MySqlConnstr.GetDBConnection()) { string sqlStr = "select id,name from tb_b_dictionary_detail where bm like '008%' order by code"; DataTable dt = dbc.ExecuteDataTable(sqlStr); return(dt); } }
public object GetUserList2(int pagnum, int pagesize, string stime, string etime, string username) { using (MySqlDbConnection dbc = MySqlConnstr.GetDBConnection()) { try { int cp = pagnum; int ac = 0; string where = ""; if (!string.IsNullOrEmpty(username)) { where += " and " + dbc.C_Like("f.username", username.Trim(), LikeStyle.LeftAndRightLike); } if (stime != null && stime != "") { where += " and c.shippingnoteadddatetime>=" + dbc.ToSqlValue(stime); } if (etime != null && etime != "") { where += " and c.shippingnoteadddatetime<=" + dbc.ToSqlValue(etime); } string str = @" SELECT d.billingid,e.actualmoney,f.username,f.userid,c.shippingnoteid,c.shippingnoteadddatetime,c.shippingnotenumber,c.statisticstype,d.totalamount,d.totalvaloremtax,d.rate,d.billingtime,d.invoicecode,d.invoicenumber FROM tb_b_shippingnoteinfo c LEFT JOIN (SELECT a.shippingnoteid,b.totalvaloremtax,b.rate,b.billingtime,b.invoicecode,b.invoicenumber,b.totalamount,b.invoicestatus,b.billingid FROM tb_b_invoicedetail a LEFT JOIN tb_b_invoice b ON a.billingid=b.billingid ) d ON c.shippingnoteid=d.shippingnoteid LEFT JOIN tb_b_sourcegoodsinfo_offer e ON c.offerid=e.offerid LEFT JOIN tb_b_user f ON e.shipperid=f.userid WHERE d.invoicestatus=0 AND c.shippingnotestatuscode = 90 "; str += where; //开始取分页数据 System.Data.DataTable dtPage = new System.Data.DataTable(); dtPage = dbc.GetPagedDataTable(str + " ORDER BY c.consignmentdatetime", pagesize, ref cp, out ac); return(new { dt = dtPage, cp = cp, ac = ac }); } catch (Exception ex) { throw ex; } } }
public object getSourceGoodsListByPage(int pagnum, int pagesize, string beg, string end, string changjia, string offerstatus, string flowstatus) { using (MySqlDbConnection dbc = MySqlConnstr.GetDBConnection()) { try { int cp = pagnum; int ac = 0; string where = ""; if (!string.IsNullOrEmpty(beg)) { where += " and a.addtime>='" + Convert.ToDateTime(beg).ToString("yyyy-MM-dd") + "'"; } if (!string.IsNullOrEmpty(end)) { where += " and a.addtime<='" + Convert.ToDateTime(end).AddDays(1).ToString("yyyy-MM-dd") + "'"; } if (!string.IsNullOrEmpty(changjia.Trim())) { where += " and " + dbc.C_Like("b.username", changjia.Trim(), SmartFramework4v2.Data.LikeStyle.LeftAndRightLike); } if (!string.IsNullOrEmpty(offerstatus)) { where += " and " + dbc.C_EQ("a.offerstatus", offerstatus.Trim()); } if (!string.IsNullOrEmpty(flowstatus)) { where += " and " + dbc.C_EQ("a.flowstatus", flowstatus.Trim()); } string str = @"select a.*,b.username,b.carriername,c.name vehiclelengthrequirementname from tb_b_sourcegoodsinfo_offer a left join tb_b_user b on a.shipperid=b.userid left join tb_b_dictionary_detail c on a.vehiclelengthrequirement=c.bm where (a.shipperid in( select d.userid from tb_b_operator_association d left join tb_b_user e on d.userid=e.userid inner join tb_b_user f on d.operator=f.userid and f.correlationid=" + dbc.ToSqlValue(SystemUser.CurrentUser.UserID) + @" where d.status = 0 ) or 'D4D659F2-C2AE-4D96-AA87-A5DF0EC3F57C'=" + dbc.ToSqlValue(SystemUser.CurrentUser.UserID.ToUpper()) + @")"; str += where; //开始取分页数据 System.Data.DataTable dtPage = new System.Data.DataTable(); dtPage = dbc.GetPagedDataTable(str + " order by a.offerstatus asc, a.flowstatus asc, a.goodsinsertdatetime desc", pagesize, ref cp, out ac); return(new { dt = dtPage, cp = cp, ac = ac }); } catch (Exception ex) { throw ex; } } }
public void SaveGoods(JSReader jsr) { if (string.IsNullOrEmpty(jsr["goodstypeid"].ToString())) { throw new Exception("货品类别不能为空"); } if (jsr["goodsname"].IsNull || jsr["goodsname"].IsEmpty) { throw new Exception("货品名称不能为空"); } using (MySqlDbConnection dbc = MySqlConnstr.GetDBConnection()) { try { if (string.IsNullOrEmpty(jsr["goodsid"].ToString())) { var goodsid = Guid.NewGuid().ToString(); var dt = dbc.GetEmptyDataTable("tb_b_goods"); var dr = dt.NewRow(); dr["goodsid"] = goodsid; dr["goodsname"] = jsr["goodsname"].ToString(); dr["goodstype"] = jsr["goodstypeid"].ToString(); dr["status"] = 0; dr["adduser"] = SystemUser.CurrentUser.UserID; dr["addtime"] = DateTime.Now; dr["updateuser"] = SystemUser.CurrentUser.UserID; dr["updatetime"] = DateTime.Now; dt.Rows.Add(dr); dbc.InsertTable(dt); } else { var dt = dbc.GetEmptyDataTable("tb_b_goods"); var dtt = new SmartFramework4v2.Data.DataTableTracker(dt); var dr = dt.NewRow(); dr["goodsid"] = jsr["goodsid"].ToString(); dr["goodsname"] = jsr["goodsname"].ToString(); dr["goodstype"] = jsr["goodstypeid"].ToString(); dr["updateuser"] = SystemUser.CurrentUser.UserID; dr["updatetime"] = DateTime.Now; dt.Rows.Add(dr); dbc.UpdateTable(dt, dtt); } } catch (Exception ex) { throw ex; } } }
public byte[] ExportPriceModelTemplate(string goodsid) { using (MySqlDbConnection dbc = MySqlConnstr.GetDBConnection()) { try { Workbook workbook = new Workbook(System.Web.HttpContext.Current.Server.MapPath("~/Mb/价格模板.xls")); //工作簿 Worksheet sheet = workbook.Worksheets[0]; //工作表 Cells cells = sheet.Cells; //单元格 string sqlstr = @"select a.goodstypeid,a.goodsid,a.statisticstype,b.sheng1,b.shi1,b.qu1,c.sheng2,c.shi2,c.qu2,a.frompart,a.topart,a.vehicletyperequirement,a.vehiclelengthrequirement,a.price,a.pickprice,a.deliverprice from tb_b_pricemodel a left join( select t1.`code`,t3.`NAME` sheng1,t2.`NAME` shi1,t1.`NAME` qu1 from tb_b_area t1,tb_b_area t2 ,tb_b_area t3 where t1.pcode=t2.`code` and t2.pcode=t3.`code` )b on a.fromroutecode=b.`code` left join( select t1.`code`,t3.`NAME` sheng2,t2.`NAME` shi2,t1.`NAME` qu2 from tb_b_area t1,tb_b_area t2 ,tb_b_area t3 where t1.pcode=t2.`code` and t2.pcode=t3.`code` )c on a.toroutecode=c.`code` where goodsid=" + dbc.ToSqlValue(goodsid); DataTable dt = dbc.ExecuteDataTable(sqlstr); int excelEditRow = 2; for (int i = 0; i < dt.Rows.Count; i++) { cells[excelEditRow + i, 0].PutValue(dt.Rows[i]["goodstypeid"].ToString()); cells[excelEditRow + i, 1].PutValue(dt.Rows[i]["goodsid"].ToString()); cells[excelEditRow + i, 2].PutValue((string.IsNullOrEmpty(dt.Rows[i]["statisticstype"].ToString())) ? "" : (dt.Rows[i]["statisticstype"].ToString() == "1" ? "零担" : "整车")); //运输类型(只能是零担/整车) cells[excelEditRow + i, 3].PutValue(dt.Rows[i]["sheng1"].ToString()); //起始地(省市区三个列) cells[excelEditRow + i, 4].PutValue(dt.Rows[i]["shi1"].ToString()); cells[excelEditRow + i, 5].PutValue(dt.Rows[i]["qu1"].ToString()); cells[excelEditRow + i, 6].PutValue(dt.Rows[i]["sheng2"].ToString());//目的地(省市区三个列) cells[excelEditRow + i, 7].PutValue(dt.Rows[i]["shi2"].ToString()); cells[excelEditRow + i, 8].PutValue(dt.Rows[i]["qu2"].ToString()); cells[excelEditRow + i, 9].PutValue(dt.Rows[i]["frompart"].ToString()); //范围起始值, cells[excelEditRow + i, 10].PutValue(dt.Rows[i]["topart"].ToString()); //范围结束值 cells[excelEditRow + i, 11].PutValue((string.IsNullOrEmpty(dt.Rows[i]["vehicletyperequirement"].ToString())) ? "" : (dt.Rows[i]["vehicletyperequirement"].ToString() == "1" ? "栏板车" : "厢车")); //车型(栏板车/厢车) cells[excelEditRow + i, 12].PutValue(dt.Rows[i]["vehiclelengthrequirement"].ToString()); //车长(栏板车(4.2m,6.8m,9.6m,13m,17.5m)厢车(4.2m,6.8m,9.6m,17.5m)) cells[excelEditRow + i, 13].PutValue(dt.Rows[i]["price"].ToString()); //单价 cells[excelEditRow + i, 14].PutValue(dt.Rows[i]["pickprice"].ToString()); //提货价 cells[excelEditRow + i, 15].PutValue(dt.Rows[i]["deliverprice"].ToString()); //送货价 } MemoryStream ms = workbook.SaveToStream(); byte[] bt = ms.ToArray(); return(bt); } catch (Exception ex) { throw ex; } } }
public static object GetUserMX(string userid) { using (MySqlDbConnection dbc = MySqlConnstr.GetDBConnection()) { try { return(dbc.ExecuteDataTable("select * from tb_b_user where userid=" + dbc.ToSqlValue(userid))); } catch (Exception ex) { throw ex; } } }
public object DelGoods(string goodsId) { using (MySqlDbConnection dbc = MySqlConnstr.GetDBConnection()) { try { string sqlStr = "update tb_b_goods set status=1 where goodsid=" + dbc.ToSqlValue(goodsId); dbc.ExecuteDataTable(sqlStr); return(true); } catch (Exception ex) { throw ex; } } }
public DataTable GetAreaList(string pcode) { using (MySqlDbConnection dbc = MySqlConnstr.GetDBConnection()) { try { string sql = @"SELECT * FROM tb_b_area WHERE pcode=" + dbc.ToSqlValue(pcode) + " order by `code`"; DataTable dt = dbc.ExecuteDataTable(sql); return(dt); } catch (Exception ex) { throw ex; } } }
public DataTable GetHXLIST(string shippingnoteid, string costid) { using (MySqlDbConnection dbc = MySqlConnstr.GetDBConnection()) { try { var dt = dbc.ExecuteDataTable(@"SELECT a.*,b.`username` FROM tb_b_shippingnoteinfo_verify a LEFT JOIN tb_b_user b ON a.`userid`=b.`userid` WHERE costid=" + dbc.ToSqlValue(costid) + @" and shippingnoteid=" + dbc.ToSqlValue(shippingnoteid) + @" ORDER BY verifytime DESC"); return(dt); } catch (Exception ex) { throw ex; } } }
public static bool ChangeZF(string billingid, string shippingnoteid) { var companyId = SystemUser.CurrentUser.CompanyID; using (MySqlDbConnection dbc = MySqlConnstr.GetDBConnection()) { dbc.BeginTransaction(); try { dbc.ExecuteNonQuery("update tb_b_invoice set invoicestatus=1 where billingid=" + dbc.ToSqlValue(billingid)); var dt = dbc.ExecuteDataTable("select * from tb_b_invoice where billingid=" + dbc.ToSqlValue(billingid)); var jsr = dt.Rows[0]; var dtre = dbc.GetEmptyDataTable("tb_b_shippingnoteinfo_record"); var drre = dtre.NewRow(); drre["id"] = Guid.NewGuid().ToString(); drre["shippingnoteid"] = shippingnoteid; drre["recordtype"] = "作废发票"; drre["recordmemo"] = "作废发票时间:" + DateTime.Now.ToString("yyyy-MM-dd") + ",票号:" + jsr["invoicenumber"].ToString() + ",金额:" + jsr["totalamount"].ToString() + ",代码:" + jsr["invoicecode"].ToString(); drre["status"] = 0; drre["adduser"] = SystemUser.CurrentUser.UserID; drre["updateuser"] = SystemUser.CurrentUser.UserID; drre["addtime"] = DateTime.Now; drre["updatetime"] = DateTime.Now; dtre.Rows.Add(drre); dbc.InsertTable(dtre); dbc.CommitTransaction(); } catch (Exception ex) { dbc.RoolbackTransaction(); throw ex; } } return(true); }
public bool UpdateMktOffer(string offerid, JSReader jsr) { using (MySqlDbConnection dbc = MySqlConnstr.GetDBConnection()) { try { dbc.BeginTransaction(); String totalmonetaryamount = jsr["totalmonetaryamount"].ToString(); String estimatemoney = jsr["estimatemoney"].ToString(); String estimatecompletemoney = jsr["estimatecompletemoney"].ToString(); String estimatetaxmoney = jsr["estimatetaxmoney"].ToString(); String estimatecostmoney = jsr["estimatecostmoney"].ToString(); string sql = "update tb_b_sourcegoodsinfo_offer set flowstatus = 90,totalmonetaryamount=" + dbc.ToSqlValue(totalmonetaryamount) + ",estimatemoney=" + dbc.ToSqlValue(estimatemoney) + ",estimatecompletemoney=" + dbc.ToSqlValue(estimatecompletemoney) + ",estimatetaxmoney=" + dbc.ToSqlValue(estimatetaxmoney) + ",estimatecostmoney=" + dbc.ToSqlValue(estimatecostmoney) + " where offerid=" + dbc.ToSqlValue(offerid); dbc.ExecuteNonQuery(sql); DateTime ti = DateTime.Now; var dt = dbc.GetEmptyDataTable("tb_b_sourcegoodsinfo_offer_flow"); var newDr = dt.NewRow(); newDr["flowid"] = Guid.NewGuid(); newDr["offerid"] = offerid; newDr["flowstatus"] = 90; newDr["status"] = 0; newDr["adduser"] = SystemUser.CurrentUser.UserID; newDr["addtime"] = ti; newDr["updateuser"] = SystemUser.CurrentUser.UserID; newDr["updatetime"] = ti; dt.Rows.Add(newDr); dbc.InsertTable(dt); /*insert into tb_b_sourcegoodsinfo_offer_record;recrodtype = "市场部询价单已提交企业",recordmemo = "xxx 在xxx时间 市场部询价单已提交企业";*/ LogBySourcegoodsinfoOffer(dbc, offerid, "市场部询价单已提交企业", SystemUser.CurrentUser.UserName + "在" + ti + "市场部询价单已提交企业", ti); dbc.CommitTransaction(); return(true); } catch (Exception ex) { dbc.RoolbackTransaction(); throw ex; } } }
public static object GetDDMX(string shippingnotenumber) { using (MySqlDbConnection dbc = MySqlConnstr.GetDBConnection()) { try { var sql = @" SELECT c.*,b.* FROM tb_b_sourcegoodsinfo_offer b LEFT JOIN tb_b_shippingnoteinfo a ON a.offerid=b.offerid LEFT JOIN tb_b_user c ON a.takegoodsdriver =c.userid where b.shippingnotenumber=" + dbc.ToSqlValue(shippingnotenumber); var dt = dbc.ExecuteDataTable(sql); return(dt); } catch (Exception ex) { throw ex; } } }
private string Verify(string _goodstypeid, string _goodsid, string _statisticstype, string _fromroutecode, string _toroutecode, string _vehicletyperequirement, string _vehiclelengthrequirement) { using (MySqlDbConnection dbc = MySqlConnstr.GetDBConnection()) { string id = ""; string sql = @"select * from tb_b_pricemodel where status=0 and goodstypeid = " + dbc.ToSqlValue(_goodstypeid) + " and goodsid=" + dbc.ToSqlValue(_goodsid) + " and statisticstype=" + dbc.ToSqlValue(_statisticstype) + " and fromroutecode=" + dbc.ToSqlValue(_fromroutecode) + " and toroutecode=" + dbc.ToSqlValue(_toroutecode) + " and vehicletyperequirement=" + dbc.ToSqlValue(_vehicletyperequirement) + " and vehiclelengthrequirement= " + dbc.ToSqlValue(_vehiclelengthrequirement); DataTable dt = dbc.ExecuteDataTable(sql); if (dt.Rows.Count > 0) { id = dt.Rows[0]["id"].ToString(); } return(id); } }
public static object GetDDMX(string shippingnotenumber) { using (MySqlDbConnection dbc = MySqlConnstr.GetDBConnection()) { try { var sql = @" SELECT c.*,b.*,e.name vehiclelengthrequirementname,f.vehiclenumber FROM tb_b_sourcegoodsinfo_offer b LEFT JOIN tb_b_user c ON b.shipperid=c.userid LEFT JOIN tb_b_dictionary_detail e ON b.vehiclelengthrequirement=e.bm LEFT JOIN tb_b_locationinfo f ON b.shippingnotenumber=f.shippingnotenumber where b.shippingnotenumber=" + dbc.ToSqlValue(shippingnotenumber); var dt = dbc.ExecuteDataTable(sql); return(dt); } catch (Exception ex) { throw ex; } } }
public DataTable GetGoodsLine(string goodstype, string goodsName) { using (MySqlDbConnection dbc = MySqlConnstr.GetDBConnection()) { try { string where = ""; if (!string.IsNullOrEmpty(goodsName)) { where += " and " + dbc.C_Like("a.goodsname", goodsName.Trim(), LikeStyle.LeftAndRightLike); } string sql = @"select a.*,(select count(1) from tb_b_pricemodel where goodsid = a.goodsid and status=0) priceNum,b.name goodsTypeName from tb_b_goods a left join tb_b_dictionary_detail b on a.goodstype=b.id where a.status=0 and a.goodstype = " + dbc.ToSqlValue(goodstype) + where; DataTable dt = dbc.ExecuteDataTable(sql); return(dt); } catch (Exception ex) { throw ex; } } }
public bool DelGoodsPrice(JSReader jsr) { using (MySqlDbConnection dbc = MySqlConnstr.GetDBConnection()) { dbc.BeginTransaction(); try { for (int i = 0; i < jsr.ToArray().Length; i++) { string sqlStr = "update tb_b_pricemodel set status=1 where id=" + dbc.ToSqlValue(jsr.ToArray()[i].ToString()); dbc.ExecuteDataTable(sqlStr); } dbc.CommitTransaction(); return(true); } catch (Exception ex) { dbc.RoolbackTransaction(); throw ex; } } }
public bool ToWritePrice(String offerid) { using (MySqlDbConnection dbc = MySqlConnstr.GetDBConnection()) { try { dbc.BeginTransaction(); string sql = "update tb_b_sourcegoodsinfo_offer set flowstatus=10,businessid=" + dbc.ToSqlValue(SystemUser.CurrentUser.MQUserID) + " where offerid=" + dbc.ToSqlValue(offerid); dbc.ExecuteNonQuery(sql); DateTime ti = DateTime.Now; var dt = dbc.GetEmptyDataTable("tb_b_sourcegoodsinfo_offer_flow"); var newDr = dt.NewRow(); newDr["flowid"] = Guid.NewGuid(); newDr["offerid"] = offerid; newDr["flowstatus"] = 10; newDr["status"] = 0; newDr["adduser"] = SystemUser.CurrentUser.UserID; newDr["addtime"] = ti; newDr["updateuser"] = SystemUser.CurrentUser.UserID; newDr["updatetime"] = ti; dt.Rows.Add(newDr); dbc.InsertTable(dt); /*insert into tb_b_sourcegoodsinfo_offer_record;recrodtype = "询价单市场部转操作部",recordmemo = "xxx 在xxx时间 询价单市场部转操作部*/ LogBySourcegoodsinfoOffer(dbc, offerid, "询价单市场部转操作部", SystemUser.CurrentUser.UserName + "在" + ti + "询价单市场部转操作部", ti); dbc.CommitTransaction(); return(true); } catch (Exception ex) { dbc.RoolbackTransaction(); throw ex; } } }
public object GetGoodsList(int pagnum, int pagesize, string goodsTypeId, string goodsName) { using (MySqlDbConnection dbc = MySqlConnstr.GetDBConnection()) { try { int cp = pagnum; int ac = 0; string where = ""; if (!string.IsNullOrEmpty(goodsTypeId.Trim())) { where += " and " + dbc.C_EQ("a.goodstype", goodsTypeId.Trim()); } if (!string.IsNullOrEmpty(goodsName.Trim())) { where += " and " + dbc.C_Like("a.goodsname", goodsName.Trim(), LikeStyle.LeftAndRightLike); } string str = @"select a.*,b.id goodstypeid,b.name goodstypename from tb_b_goods a left join tb_b_dictionary_detail b on a.goodstype=b.id where a.status=0 "; str += where; //开始取分页数据 System.Data.DataTable dtPage = new System.Data.DataTable(); dtPage = dbc.GetPagedDataTable(str + " order by b.code asc", pagesize, ref cp, out ac); return(new { dt = dtPage, cp = cp, ac = ac }); } catch (Exception ex) { throw ex; } } }
public object UploadPriceModel(FileData[] fds, string goodsid, string goodsname, string goodstypeid) { using (MySqlDbConnection dbc = MySqlConnstr.GetDBConnection()) { try { dbc.BeginTransaction(); string str = ""; if (fds[0].FileBytes.Length == 0) { throw new Exception("你上传的文件可能已被打开,请关闭该文件!"); } System.IO.MemoryStream ms = new System.IO.MemoryStream(fds[0].FileBytes); Workbook workbook = new Workbook(ms); Worksheet sheet = workbook.Worksheets[0]; Cells cells = sheet.Cells; //foreach (Cell cell in cells) //{ // if (cell.IsMerged == true) // { // Range range = cell.GetMergedRange(); // cell.Value = cells[range.FirstRow, range.FirstColumn].Value; // } // else // { // cell.Value = cell.Value; // } //} DataTable mydt = cells.ExportDataTableAsString(2, 0, cells.MaxRow + 1, cells.MaxColumn + 1); //数据准备 string sql = "select * from tb_b_area "; DataTable arasDt = dbc.ExecuteDataTable(sql); sql = "select * from tb_b_dictionary_detail"; DataTable dicDt = dbc.ExecuteDataTable(sql); //遍历验证 for (int i = 0; i < mydt.Rows.Count; i++) { DataRow dr = mydt.Rows[i]; if (!string.IsNullOrEmpty(dr[2].ToString())) { string _goodstypeid = dr[0].ToString().Trim(); string _goodsid = dr[1].ToString().Trim(); string _statisticstype = (string.IsNullOrEmpty(dr[2].ToString().Trim())) ? "" : (dr[2].ToString().Trim() == "零担" ? "1" : "2");//运输类型(只能是零担/整车) //= dr[3].ToString();//起始地(省市区三个列) //= dr[4].ToString(); string _fromroutecode = ""; string _fromroutename = dr[5].ToString().Trim(); if (!string.IsNullOrEmpty(_fromroutename)) { _fromroutecode = arasDt.Select("name='" + _fromroutename + "'")[0]["code"].ToString(); } //= dr[6].ToString();//目的地(省市区三个列) //= dr[7].ToString(); string _toroutecode = ""; string _toroutename = dr[8].ToString().Trim(); if (!string.IsNullOrEmpty(_toroutename)) { _toroutecode = arasDt.Select("name='" + _toroutename + "'")[0]["code"].ToString(); } string _frompart = dr[9].ToString().Trim(); //范围起始值 string _topart = dr[10].ToString().Trim(); //范围结束值 string _vehicletyperequirement = (string.IsNullOrEmpty(dr[11].ToString().Trim())) ? "" : (dr[11].ToString().Trim() == "栏板车" ? "1" : "2"); //车型(栏板车/厢车) string _vehiclelengthrequirement = ""; //车长 DataRow[] ccDrs = dicDt.Select("name=" + dr[12].ToString().Trim()); if (ccDrs.Length > 0) { _vehiclelengthrequirement = ccDrs[0]["bm"].ToString(); } string _price = dr[13].ToString().Trim(); //单价 string _pickprice = dr[14].ToString().Trim(); //提货价 string _deliverprice = dr[15].ToString().Trim(); //送货价 #region 验证 switch (_statisticstype) { case "1": //如果运输类型为零担,起始地,目的地,范围起始值,范围结束值,单价,提货价,送货价为必填 if (string.IsNullOrEmpty(_fromroutename)) { str = "请填写起始地!"; } if (string.IsNullOrEmpty(_toroutename)) { str = "请填写目的地!"; } if (string.IsNullOrEmpty(_frompart)) { str = "请填写范围起始值!"; } if (string.IsNullOrEmpty(_topart)) { str = "请填写范围结束值!"; } if (string.IsNullOrEmpty(_price)) { str = "请填写单价!"; } if (string.IsNullOrEmpty(_pickprice)) { str = "请填写提货价!"; } if (string.IsNullOrEmpty(_deliverprice)) { str = "请填写送货价!"; } break; case "2": //如果运输类型为整车,起始地(省市区三个列),目的地(省市区三个列),车型,车长,单价,提货价,送货价为必填 if (string.IsNullOrEmpty(_fromroutename)) { str = "请填写起始地!"; } if (string.IsNullOrEmpty(_toroutename)) { str = "请填写目的地!"; } if (string.IsNullOrEmpty(_vehicletyperequirement)) { str = "请填写车型!"; } if (string.IsNullOrEmpty(_vehiclelengthrequirement)) { str = "请填写车长!"; } if (string.IsNullOrEmpty(_price)) { str = "请填写单价!"; } if (string.IsNullOrEmpty(_pickprice)) { str = "请填写提货价!"; } if (string.IsNullOrEmpty(_deliverprice)) { str = "请填写送货价!"; } break; } #endregion if (!string.IsNullOrEmpty(str)) { throw new Exception("你上传的文件填写数据有误【" + (i + 3) + "行" + str + "】"); } #region 数据添加或修改 /**导入时先根据goodstypeid,goodsid,运输类型,起始地,目的地,车型,车长搜索一下, * 如果存在,则更新单价,提货价,送货价, * 如果不存在,则插入新的数据,省市区要查询地区表,查询转换成code插入对应地区编号 */ string _id = Verify(_goodstypeid, _goodsid, _statisticstype, _fromroutecode, _toroutecode, _vehicletyperequirement, _vehiclelengthrequirement); if (!string.IsNullOrEmpty(_id)) { //这里还需判断导入表货物与表货物一致 if (_goodstypeid != goodstypeid || _goodsid != goodsid) { throw new Exception("你上传的文件填写数据有误【当前表数据与货物不一致】"); } var dt = dbc.GetEmptyDataTable("tb_b_pricemodel"); var dtt = new SmartFramework4v2.Data.DataTableTracker(dt); var upDr = dt.NewRow(); upDr["id"] = _id; upDr["goodsname"] = goodsname; upDr["goodstypeid"] = _goodstypeid; upDr["goodsid"] = _goodsid; upDr["fromroutecode"] = _fromroutecode; upDr["fromroutename"] = _fromroutename; upDr["toroutecode"] = _toroutecode; upDr["toroutename"] = _toroutename; upDr["price"] = !string.IsNullOrEmpty(_price) ? Convert.ToDecimal(_price) : 0m; upDr["pickprice"] = !string.IsNullOrEmpty(_pickprice) ? Convert.ToDecimal(_pickprice) : 0m; upDr["deliverprice"] = !string.IsNullOrEmpty(_deliverprice) ? Convert.ToDecimal(_deliverprice) : 0m; if (!string.IsNullOrEmpty(_frompart)) { upDr["frompart"] = _frompart; } if (!string.IsNullOrEmpty(_topart)) { upDr["topart"] = _topart; } upDr["status"] = 0; upDr["updateuser"] = SystemUser.CurrentUser.UserID; upDr["updatetime"] = DateTime.Now; if (!string.IsNullOrEmpty(_statisticstype)) { upDr["statisticstype"] = Convert.ToInt32(_statisticstype); } upDr["vehicletyperequirement"] = _vehicletyperequirement; upDr["vehiclelengthrequirement"] = _vehiclelengthrequirement; dt.Rows.Add(upDr); dbc.UpdateTable(dt, dtt); } else { var id = Guid.NewGuid().ToString(); var dt = dbc.GetEmptyDataTable("tb_b_pricemodel"); var inDr = dt.NewRow(); inDr["id"] = id; inDr["goodsname"] = goodsname; inDr["goodstypeid"] = goodstypeid; inDr["goodsid"] = goodsid; inDr["fromroutecode"] = _fromroutecode; inDr["fromroutename"] = _fromroutename; inDr["toroutecode"] = _toroutecode; inDr["toroutename"] = _toroutename; inDr["price"] = !string.IsNullOrEmpty(_price) ? Convert.ToDecimal(_price) : 0m; inDr["pickprice"] = !string.IsNullOrEmpty(_pickprice) ? Convert.ToDecimal(_pickprice) : 0m; inDr["deliverprice"] = !string.IsNullOrEmpty(_deliverprice) ? Convert.ToDecimal(_deliverprice) : 0m; if (!string.IsNullOrEmpty(_frompart)) { inDr["frompart"] = _frompart; } if (!string.IsNullOrEmpty(_topart)) { inDr["topart"] = _topart; } inDr["status"] = 0; inDr["adduser"] = SystemUser.CurrentUser.UserID; inDr["addtime"] = DateTime.Now; inDr["updateuser"] = SystemUser.CurrentUser.UserID; inDr["updatetime"] = DateTime.Now; if (!string.IsNullOrEmpty(_statisticstype)) { inDr["statisticstype"] = Convert.ToInt32(_statisticstype); } inDr["vehicletyperequirement"] = _vehicletyperequirement; inDr["vehiclelengthrequirement"] = _vehiclelengthrequirement; dt.Rows.Add(inDr); dbc.InsertTable(dt); } #endregion } } sql = @"select id,CASE statisticstype WHEN 1 THEN '零担' WHEN 2 THEN '整车' ELSE '' END transporttype, fromroutecode,fromroutename,toroutecode,toroutename,price,pickprice,deliverprice,frompart,topart from tb_b_pricemodel where goodsid=" + dbc.ToSqlValue(goodsid) + " and status=0 order by updatetime desc"; DataTable retDt = dbc.ExecuteDataTable(sql); dbc.CommitTransaction(); return(new { dt = retDt, str = str }); } catch (Exception ex) { dbc.RoolbackTransaction(); throw ex; } } }
public byte[] GetGoodsPriceLineToFile(string goodsId, string fromroute, string toroute, string statisticstype) { using (MySqlDbConnection dbc = MySqlConnstr.GetDBConnection()) { try { Workbook workbook = new Workbook(); //工作簿 Worksheet sheet = workbook.Worksheets[0]; //工作表 Cells cells = sheet.Cells; //单元格 //为标题设置样式 Style styleTitle = workbook.Styles[workbook.Styles.Add()]; styleTitle.HorizontalAlignment = TextAlignmentType.Center; //文字居中 styleTitle.Font.Name = "宋体"; //文字字体 styleTitle.Font.Size = 18; //文字大小 styleTitle.Font.IsBold = true; //粗体 //样式1 Style style1 = workbook.Styles[workbook.Styles.Add()]; style1.HorizontalAlignment = TextAlignmentType.Center; //文字居中 style1.Font.Name = "宋体"; //文字字体 style1.Font.Size = 12; //文字大小 style1.Font.IsBold = true; //粗体 //样式2 Style style2 = workbook.Styles[workbook.Styles.Add()]; style2.HorizontalAlignment = TextAlignmentType.Left; //文字居中 style2.Font.Name = "宋体"; //文字字体 style2.Font.Size = 14; //文字大小 style2.Font.IsBold = true; //粗体 style2.IsTextWrapped = true; //单元格内容自动换行 style2.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin; //应用边界线 左边界线 style2.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin; //应用边界线 右边界线 style2.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin; //应用边界线 上边界线 style2.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin; //应用边界线 下边界线 style2.IsLocked = true; //样式3 Style style4 = workbook.Styles[workbook.Styles.Add()]; style4.HorizontalAlignment = TextAlignmentType.Left; //文字居中 style4.Font.Name = "宋体"; //文字字体 style4.Font.Size = 11; //文字大小 style4.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin; style4.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin; style4.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin; style4.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin; /*goodstypeid,goodsid,运输类型(只能是零担/整车),起始地(省市区三个列),目的地(省市区三个列), * 范围起始值,范围结束值,车型(栏板车/厢车),车长(栏板车(4.2m,6.8m,9.6m,13m,17.5m)厢车(4.2m,6.8m,9.6m,17.5m)),单价,提货价,送货价*/ cells.SetRowHeight(0, 20); cells[0, 0].PutValue("货物分类"); cells[0, 0].SetStyle(style2); cells.SetColumnWidth(0, 20); cells[0, 1].PutValue("货物名称"); cells[0, 1].SetStyle(style2); cells.SetColumnWidth(1, 20); cells[0, 2].PutValue("价格种类数量"); cells[0, 2].SetStyle(style2); cells.SetColumnWidth(2, 20); DataTable dt = GetGoodsPriceLine(goodsId, fromroute, toroute, statisticstype); for (int i = 0; i < dt.Rows.Count; i++) { cells[i + 1, 0].PutValue(dt.Rows[i]["goodsTypeName"]); cells[i + 1, 0].SetStyle(style4); cells[i + 1, 1].PutValue(dt.Rows[i]["goodsname"]); cells[i + 1, 1].SetStyle(style4); cells[i + 1, 2].PutValue(dt.Rows[i]["priceNum"]); cells[i + 1, 2].SetStyle(style4); } MemoryStream ms = workbook.SaveToStream(); byte[] bt = ms.ToArray(); return(bt); } catch (Exception ex) { throw ex; } } }
public static bool HXMoneyALL(JSReader jsr, JSReader jsr2) { var companyId = SystemUser.CurrentUser.CompanyID; using (MySqlDbConnection dbc = MySqlConnstr.GetDBConnection()) { dbc.BeginTransaction(); try { for (int i = 0; i < jsr.ToArray().Length; i++) { string shippingnoteid = ""; string userid = ""; string offerid = ""; string actualdrivermoney = ""; string username = ""; string verifymoney = ""; string costid = ""; string[] arr = jsr.ToArray()[i].ToString().Split(','); if (arr.Length > 0) { shippingnoteid = arr[0].ToString(); userid = arr[1].ToString(); offerid = arr[2].ToString(); actualdrivermoney = arr[3].ToString(); if (arr[4].ToString() != null && arr[4].ToString() != "" && arr[4].ToString() != "null") { verifymoney = arr[4].ToString(); } else { verifymoney = "0"; } username = arr[5].ToString(); costid = arr[6].ToString(); } string ye = (Convert.ToDouble(actualdrivermoney) - Convert.ToDouble(verifymoney)).ToString(); if (ye == "0") { //dbc.ExecuteNonQuery("update tb_b_sourcegoodsinfo_offer set carrierverifystatus = 2,carrierverifymoney =" + dbc.ToSqlValue(actualdrivermoney) + " where offerid=" + dbc.ToSqlValue(offerid)); dbc.ExecuteNonQuery("update tb_b_shippingnoteinfo_cost set verifystatus = 2,verifymoney =" + dbc.ToSqlValue(actualdrivermoney) + " where id=" + dbc.ToSqlValue(costid)); } else { var dts = dbc.ExecuteDataTable("SELECT verifymoney,verifystatus FROM tb_b_shippingnoteinfo_cost WHERE id=" + dbc.ToSqlValue(costid)); if (dts.Rows.Count > 0) { if (dts.Rows[0][0] != null && dts.Rows[0][0].ToString() != "") { // dbc.ExecuteNonQuery("update tb_b_sourcegoodsinfo_offer set carrierverifystatus = 1,carrierverifymoney =carrierverifymoney+" + dbc.ToSqlValue(jsr["verifymoney"].ToString()) + " where offerid=" + dbc.ToSqlValue(offerid)); dbc.ExecuteNonQuery("update tb_b_shippingnoteinfo_cost set verifystatus = 1,verifymoney =verifymoney+" + dbc.ToSqlValue(jsr["verifymoney"].ToString()) + " where id=" + dbc.ToSqlValue(costid)); } else { // dbc.ExecuteNonQuery("update tb_b_sourcegoodsinfo_offer set carrierverifystatus = 1,carrierverifymoney =" + dbc.ToSqlValue(jsr["verifymoney"].ToString()) + " where offerid=" + dbc.ToSqlValue(offerid)); dbc.ExecuteNonQuery("update tb_b_shippingnoteinfo_cost set verifystatus = 1,verifymoney =" + dbc.ToSqlValue(jsr["verifymoney"].ToString()) + " where id=" + dbc.ToSqlValue(costid)); } } } //if (ye == "0") //{ // dbc.ExecuteNonQuery("update tb_b_sourcegoodsinfo_offer set carrierverifystatus = 2,carrierverifymoney =" + dbc.ToSqlValue(actualdrivermoney) + " where offerid=" + dbc.ToSqlValue(offerid)); //} //else //{ // var dts = dbc.ExecuteDataTable("SELECT carrierverifymoney,carrierverifystatus FROM tb_b_sourcegoodsinfo_offer WHERE offerid=" + dbc.ToSqlValue(offerid)); // if (dts.Rows.Count > 0) // { // if (dts.Rows[0][0] != null && dts.Rows[0][0].ToString() != "") // { // dbc.ExecuteNonQuery("update tb_b_sourcegoodsinfo_offer set carrierverifystatus = 1,carrierverifymoney =carrierverifymoney+" + dbc.ToSqlValue(ye) + " where offerid=" + dbc.ToSqlValue(offerid)); // } // else // { // dbc.ExecuteNonQuery("update tb_b_sourcegoodsinfo_offer set carrierverifystatus = 1,carrierverifymoney =" + dbc.ToSqlValue(ye) + " where offerid=" + dbc.ToSqlValue(offerid)); // } // } //} var dt = dbc.GetEmptyDataTable("tb_b_shippingnoteinfo_verify"); var dr = dt.NewRow(); dr["id"] = Guid.NewGuid().ToString(); dr["shippingnoteid"] = shippingnoteid; dr["costid"] = costid; dr["verifytype"] = 2;//司机核销 dr["verifymoney"] = ye; dr["verifypaytype"] = jsr2["verifypaytype"].ToString(); dr["verifytime"] = jsr2["verifytime"].ToString(); dr["userid"] = userid; dr["status"] = 0; dr["adduser"] = SystemUser.CurrentUser.UserID; dr["addtime"] = DateTime.Now; dr["updateuser"] = SystemUser.CurrentUser.UserID; dr["updatetime"] = DateTime.Now; dt.Rows.Add(dr); dbc.InsertTable(dt); var dt2 = dbc.GetEmptyDataTable("tb_b_shippingnoteinfo_record"); var dr2 = dt2.NewRow(); dr2["id"] = Guid.NewGuid().ToString(); dr2["shippingnoteid"] = shippingnoteid; dr2["recordtype"] = "财务核销"; dr2["recordmemo"] = SystemUser.CurrentUser.UserName + "在" + Convert.ToDateTime(jsr2["verifytime"].ToString()).ToString("yyyy-MM-dd") + ",财务核销" + username + ye + "元"; dr2["status"] = 0; dr2["adduser"] = SystemUser.CurrentUser.UserID; dr2["addtime"] = DateTime.Now; dr2["updateuser"] = SystemUser.CurrentUser.UserID; dr2["updatetime"] = DateTime.Now; dt2.Rows.Add(dr2); dbc.InsertTable(dt2); } dbc.CommitTransaction(); return(true); } catch (Exception ex) { dbc.RoolbackTransaction(); throw ex; } } }
public static bool SaveKPALL(JSReader jsr, JSReader jsr2) { var companyId = SystemUser.CurrentUser.CompanyID; using (MySqlDbConnection dbc = MySqlConnstr.GetDBConnection()) { dbc.BeginTransaction(); try { for (int i = 0; i < jsr.ToArray().Length; i++) { string shippingnoteid = ""; string offerid = ""; string actualcompanypay = ""; string[] arr = jsr.ToArray()[i].ToString().Split(','); if (arr.Length > 0) { shippingnoteid = arr[0].ToString(); offerid = arr[1].ToString(); actualcompanypay = arr[2].ToString(); } var billingid = Guid.NewGuid().ToString(); var dt = dbc.GetEmptyDataTable("tb_b_invoice"); var dr = dt.NewRow(); dr["billingid"] = new Guid(billingid); dr["totalamount"] = actualcompanypay; dr["totalvaloremtax"] = actualcompanypay; dr["rate"] = jsr["rate"].ToString(); dr["billingtime"] = jsr["billingtime"].ToString(); dr["invoicecode"] = jsr["invoicecode"].ToString(); dr["invoicenumber"] = jsr["invoicenumber"].ToString(); dr["isdeleteflag"] = 0; dr["invoicestatus"] = 0; dt.Rows.Add(dr); dbc.InsertTable(dt); var dtde = dbc.GetEmptyDataTable("tb_b_invoicedetail"); var drde = dtde.NewRow(); drde["invoicedetailid"] = Guid.NewGuid().ToString(); drde["billingid"] = new Guid(billingid); drde["billingitem"] = jsr["billingitem"].ToString(); drde["amount"] = actualcompanypay; drde["shippingnoteid"] = shippingnoteid; drde["isdeleteflag"] = 0; dtde.Rows.Add(drde); dbc.InsertTable(dtde); var dtre = dbc.GetEmptyDataTable("tb_b_shippingnoteinfo_record"); var drre = dtre.NewRow(); drre["id"] = Guid.NewGuid().ToString(); drre["shippingnoteid"] = shippingnoteid; drre["recordtype"] = "开票"; drre["recordmemo"] = "开票时间:" + jsr["billingtime"].ToString() + ",票号:" + jsr["invoicenumber"].ToString() + ",金额:" + actualcompanypay + ",代码:" + jsr["invoicecode"].ToString(); drre["status"] = 0; drre["adduser"] = SystemUser.CurrentUser.UserID; drre["updateuser"] = SystemUser.CurrentUser.UserID; drre["addtime"] = DateTime.Now; drre["updatetime"] = DateTime.Now; dtre.Rows.Add(drre); dbc.InsertTable(dtre); } dbc.CommitTransaction(); } catch (Exception ex) { dbc.RoolbackTransaction(); throw ex; } } return(true); }
public static bool HXMoney(JSReader jsr, string shippingnoteid, string userid, string username, string ye, string actualdrivermoney, string offerid, string costid) { var companyId = SystemUser.CurrentUser.CompanyID; using (MySqlDbConnection dbc = MySqlConnstr.GetDBConnection()) { dbc.BeginTransaction(); try { // if (ye == "0") { //dbc.ExecuteNonQuery("update tb_b_sourcegoodsinfo_offer set carrierverifystatus = 2,carrierverifymoney =" + dbc.ToSqlValue(actualdrivermoney) + " where offerid=" + dbc.ToSqlValue(offerid)); if (Convert.ToDouble(jsr["verifymoney"].ToString()) > 0) { dbc.ExecuteNonQuery("update tb_b_shippingnoteinfo_cost set verifystatus = 2,verifymoney =" + dbc.ToSqlValue(actualdrivermoney) + " where id=" + dbc.ToSqlValue(costid)); } else { dbc.ExecuteNonQuery("update tb_b_shippingnoteinfo_cost set verifystatus = 1,verifymoney =verifymoney+" + dbc.ToSqlValue(jsr["verifymoney"].ToString()) + " where id=" + dbc.ToSqlValue(costid)); } } else { var dts = dbc.ExecuteDataTable("SELECT verifymoney,verifystatus FROM tb_b_shippingnoteinfo_cost WHERE id=" + dbc.ToSqlValue(costid)); if (dts.Rows.Count > 0) { if (dts.Rows[0][0] != null && dts.Rows[0][0].ToString() != "") { // dbc.ExecuteNonQuery("update tb_b_sourcegoodsinfo_offer set carrierverifystatus = 1,carrierverifymoney =carrierverifymoney+" + dbc.ToSqlValue(jsr["verifymoney"].ToString()) + " where offerid=" + dbc.ToSqlValue(offerid)); dbc.ExecuteNonQuery("update tb_b_shippingnoteinfo_cost set verifystatus = 1,verifymoney =verifymoney+" + dbc.ToSqlValue(jsr["verifymoney"].ToString()) + " where id=" + dbc.ToSqlValue(costid)); } else { // dbc.ExecuteNonQuery("update tb_b_sourcegoodsinfo_offer set carrierverifystatus = 1,carrierverifymoney =" + dbc.ToSqlValue(jsr["verifymoney"].ToString()) + " where offerid=" + dbc.ToSqlValue(offerid)); dbc.ExecuteNonQuery("update tb_b_shippingnoteinfo_cost set verifystatus = 1,verifymoney =" + dbc.ToSqlValue(jsr["verifymoney"].ToString()) + " where id=" + dbc.ToSqlValue(costid)); } } } var dt = dbc.GetEmptyDataTable("tb_b_shippingnoteinfo_verify"); var dr = dt.NewRow(); dr["id"] = Guid.NewGuid().ToString(); dr["shippingnoteid"] = shippingnoteid; dr["costid"] = costid; dr["verifytype"] = 2;//司机核销 dr["verifymoney"] = jsr["verifymoney"].ToString(); dr["verifypaytype"] = jsr["verifypaytype"].ToString(); dr["verifytime"] = jsr["verifytime"].ToString(); dr["userid"] = userid; dr["status"] = 0; dr["adduser"] = SystemUser.CurrentUser.UserID; dr["addtime"] = DateTime.Now; dr["updateuser"] = SystemUser.CurrentUser.UserID; dr["updatetime"] = DateTime.Now; dt.Rows.Add(dr); dbc.InsertTable(dt); var dt2 = dbc.GetEmptyDataTable("tb_b_shippingnoteinfo_record"); var dr2 = dt2.NewRow(); dr2["id"] = Guid.NewGuid().ToString(); dr2["shippingnoteid"] = shippingnoteid; dr2["recordtype"] = "财务核销"; dr2["recordmemo"] = SystemUser.CurrentUser.UserName + "在" + Convert.ToDateTime(jsr["verifytime"].ToString()).ToString("yyyy-MM-dd") + ",财务核销" + username + jsr["verifymoney"].ToString() + "元"; dr2["status"] = 0; dr2["adduser"] = SystemUser.CurrentUser.UserID; dr2["addtime"] = DateTime.Now; dr2["updateuser"] = SystemUser.CurrentUser.UserID; dr2["updatetime"] = DateTime.Now; dt2.Rows.Add(dr2); dbc.InsertTable(dt2); dbc.CommitTransaction(); return(true); } catch (Exception ex) { dbc.RoolbackTransaction(); throw ex; } } }