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;
}
}
}
