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 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 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 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 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 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;
}
}
}
public static bool SaveKP(JSReader jsr, string shippingnoteid)
{
var companyId = SystemUser.CurrentUser.CompanyID;
using (MySqlDbConnection dbc = MySqlConnstr.GetDBConnection())
{
dbc.BeginTransaction();
try
{
var billingid = Guid.NewGuid().ToString();
var dt = dbc.GetEmptyDataTable("tb_b_invoice");
var dr = dt.NewRow();
dr["billingid"] = new Guid(billingid);
dr["totalamount"] = jsr["totalamount"].ToString();
dr["totalvaloremtax"] = jsr["totalvaloremtax"].ToString();
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"] = jsr["totalamount"].ToString();
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() + ",金额:" + 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 UpdateMktOfferNew(string offerid, JSReader jsr)
{
using (MySqlDbConnection dbc = MySqlConnstr.GetDBConnection())
{
try
{
String sql = @"select a.*,b.isneededit,b.accountrate,b.completerate,b.cashrate,b.nooilmoney,c.shippingnoteid from tb_b_sourcegoodsinfo_offer a
left join tb_b_user b on a.shipperid=b.userid
left join tb_b_shippingnoteinfo c on a.offerid=c.offerid
where a.offerid=" + dbc.ToSqlValue(offerid);
DataTable dt = dbc.ExecuteDataTable(sql);
if (dt.Rows.Count > 0)
{
dbc.BeginTransaction();
string shippingnoteid = dt.Rows[0]["shippingnoteid"].ToString();
decimal estimateautomoney = string.IsNullOrEmpty(jsr["estimateautomoney"].ToString()) ? 0 : Convert.ToDecimal(jsr["estimateautomoney"].ToString());
//int money = estimateautomoney % 10 > 0 ? (int)(estimateautomoney + 10) / 10 * 10 : (int)(estimateautomoney / 10) * 10;
sql = @"update tb_b_sourcegoodsinfo_offer set operatorid=" + dbc.ToSqlValue(SystemUser.CurrentUser.MQUserID) + ",flowstatus = 90,estimateautomoney=" + dbc.ToSqlValue(estimateautomoney) + ",totalmonetaryamount=" + dbc.ToSqlValue(estimateautomoney) + @"
where offerid=" + dbc.ToSqlValue(offerid);
dbc.ExecuteNonQuery(sql);
DateTime ti = DateTime.Now;
var dt_flow = dbc.GetEmptyDataTable("tb_b_sourcegoodsinfo_offer_flow");
var newDr = dt_flow.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_flow.Rows.Add(newDr);
dbc.InsertTable(dt_flow);
/*var dt_oper = dbc.GetEmptyDataTable("tb_b_shippingnoteinfo_operator");
* var newDr2 = dt_oper.NewRow();
* newDr2["id"] = Guid.NewGuid();
* newDr2["shippingnoteid"] = shippingnoteid;
* newDr2["userid"] = SystemUser.CurrentUser.MQUserID;
* newDr2["status"] = 0;
* newDr2["adduser"] = SystemUser.CurrentUser.UserID;
* newDr2["addtime"] = ti;
* newDr2["updateuser"] = SystemUser.CurrentUser.UserID;
* newDr2["updatetime"] = ti;
* dt_oper.Rows.Add(newDr2);
* dbc.InsertTable(dt_oper);*/
LogBySourcegoodsinfoOffer(dbc, offerid, "市场部询价单已提交企业", SystemUser.CurrentUser.UserName + "在" + ti + "市场部询价单已提交企业", ti);
dbc.CommitTransaction();
return(true);
}
return(false);
}
catch (Exception ex)
{
dbc.RoolbackTransaction();
throw ex;
}
}
}
public bool updateOfferNew(string offerid, JSReader jsr)
{
using (MySqlDbConnection dbc = MySqlConnstr.GetDBConnection())
{
try
{
String sql = @"select a.*,b.isneededit,b.accountrate,b.completerate,b.cashrate,b.nooilmoney,c.shippingnoteid,b.advancerate,b.operaterate,b.grossrate,b.oilmoneyrate,b.invoicerate from tb_b_sourcegoodsinfo_offer a
left join tb_b_user b on a.shipperid=b.userid
left join tb_b_shippingnoteinfo c on a.offerid=c.offerid
where a.offerid=" + dbc.ToSqlValue(offerid);
DataTable dt = dbc.ExecuteDataTable(sql);
if (dt.Rows.Count > 0)
{
decimal estimatemoney = string.IsNullOrEmpty(jsr["estimatemoney"].ToString()) ? 0 : Convert.ToDecimal(jsr["estimatemoney"].ToString()); //预估下游成本
decimal estimateoilmoney = string.IsNullOrEmpty(jsr["estimateoilmoney"].ToString()) ? 0 : Convert.ToDecimal(jsr["estimateoilmoney"].ToString()); //用油金额
decimal estimatevotemoney = string.IsNullOrEmpty(jsr["estimatevotemoney"].ToString()) ? 0 : Convert.ToDecimal(jsr["estimatevotemoney"].ToString()); //开票金额
/*
* estimateautomoney = （
* estimatemoney
* +（estimatemoney*tb_b_user.advancerate*实际天数）
* + estimatemoney*tb_b_user.operaterate
* + estimatemoney*tb_b_user.grossrate
* - estimateoilmoney*tb_b_user.oilmoneyrate
* - estimatevotemoney*tb_b_user.invoicerate
* ）
*/
DateTime dateNow = DateTime.Now;
DateTime dateNext = DateTime.Now.AddMonths(1);
int currentDays = DateTime.DaysInMonth(dateNow.Year, dateNow.Month) - DateTime.Today.Day + 1; //这个月还剩几天
int nextDays = DateTime.DaysInMonth(dateNext.Year, dateNext.Month); //下个月天数
int actualDays = currentDays + nextDays; //实际天数
string shippingnoteid = dt.Rows[0]["shippingnoteid"].ToString();
decimal estimateautomoney = 0m;
int isneededit = string.IsNullOrEmpty(dt.Rows[0]["isneededit"].ToString()) ? 0 : Convert.ToInt32(dt.Rows[0]["isneededit"].ToString()); //是否开启修正（0：开启；1：不开启；）默认为1
decimal accountrate = string.IsNullOrEmpty(dt.Rows[0]["accountrate"].ToString()) ? 0 : Convert.ToDecimal(dt.Rows[0]["accountrate"].ToString()); //帐期财务汇率（1-1.5）
decimal advancerate = string.IsNullOrEmpty(dt.Rows[0]["advancerate"].ToString()) ? 0 : Convert.ToDecimal(dt.Rows[0]["advancerate"].ToString()); //垫资比例 ⭐⭐
decimal operaterate = string.IsNullOrEmpty(dt.Rows[0]["operaterate"].ToString()) ? 0 : Convert.ToDecimal(dt.Rows[0]["operaterate"].ToString()); //运营比例 ⭐⭐
decimal grossrate = string.IsNullOrEmpty(dt.Rows[0]["grossrate"].ToString()) ? 0 : Convert.ToDecimal(dt.Rows[0]["grossrate"].ToString()); //毛利比例 ⭐⭐
decimal oilmoneyrate = string.IsNullOrEmpty(dt.Rows[0]["oilmoneyrate"].ToString()) ? 0 : Convert.ToDecimal(dt.Rows[0]["oilmoneyrate"].ToString()); //用油比例 ⭐⭐
decimal invoicerate = string.IsNullOrEmpty(dt.Rows[0]["invoicerate"].ToString()) ? 0 : Convert.ToDecimal(dt.Rows[0]["invoicerate"].ToString()); //开票比例 ⭐⭐
decimal completerate = string.IsNullOrEmpty(dt.Rows[0]["completerate"].ToString()) ? 0 : Convert.ToDecimal(dt.Rows[0]["completerate"].ToString()); //综合成本汇率（0-1）
int cashrate = string.IsNullOrEmpty(dt.Rows[0]["cashrate"].ToString()) ? 0 : Convert.ToInt32(dt.Rows[0]["cashrate"].ToString()); //押金成本，整数
decimal nooilmoney = string.IsNullOrEmpty(dt.Rows[0]["nooilmoney"].ToString()) ? 0 : Convert.ToDecimal(dt.Rows[0]["nooilmoney"].ToString()); //未用油汇率（0-1）
estimateautomoney = decimal.Round((estimatemoney
+ (estimatemoney * advancerate * actualDays)
+ estimatemoney * operaterate
+ estimatemoney * grossrate
- estimateoilmoney * oilmoneyrate
- estimatevotemoney * invoicerate) / completerate, 2);
int money = estimateautomoney % 10 > 0 ? (int)(estimateautomoney + 10) / 10 * 10 : (int)(estimateautomoney / 10) * 10;
dbc.BeginTransaction();
if (isneededit == 0)
{
sql = @"update tb_b_sourcegoodsinfo_offer set operatorid=" + dbc.ToSqlValue(SystemUser.CurrentUser.MQUserID) + ",flowstatus = 20,estimateautomoney=" + dbc.ToSqlValue(money) + ",estimatemoney=" + dbc.ToSqlValue(estimatemoney) + @"
where offerid=" + dbc.ToSqlValue(offerid);
dbc.ExecuteNonQuery(sql);
DateTime ti = DateTime.Now;
var dt_flow = dbc.GetEmptyDataTable("tb_b_sourcegoodsinfo_offer_flow");
var newDr = dt_flow.NewRow();
newDr["flowid"] = Guid.NewGuid();
newDr["offerid"] = offerid;
newDr["flowstatus"] = 20;
newDr["status"] = 0;
newDr["adduser"] = SystemUser.CurrentUser.UserID;
newDr["addtime"] = ti;
newDr["updateuser"] = SystemUser.CurrentUser.UserID;
newDr["updatetime"] = ti;
dt_flow.Rows.Add(newDr);
dbc.InsertTable(dt_flow);
LogBySourcegoodsinfoOffer(dbc, offerid, "操作部已填写询价提交给市场部", SystemUser.CurrentUser.UserName + "在" + ti + "操作部已填写询价提交给市场部", ti);
}
else if (isneededit == 1)
{
sql = @"update tb_b_sourcegoodsinfo_offer set operatorid=" + dbc.ToSqlValue(SystemUser.CurrentUser.MQUserID) + ",flowstatus = 90,totalmonetaryamount=" + dbc.ToSqlValue(money) + ",estimatemoney=" + dbc.ToSqlValue(estimatemoney) + @"
where offerid=" + dbc.ToSqlValue(offerid);
dbc.ExecuteNonQuery(sql);
DateTime ti = DateTime.Now;
var dt_flow = dbc.GetEmptyDataTable("tb_b_sourcegoodsinfo_offer_flow");
var newDr = dt_flow.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_flow.Rows.Add(newDr);
dbc.InsertTable(dt_flow);
/*var dt_oper = dbc.GetEmptyDataTable("tb_b_shippingnoteinfo_operator");
* var newDr2 = dt_oper.NewRow();
* newDr2["id"] = Guid.NewGuid();
* newDr2["shippingnoteid"] = shippingnoteid;
* newDr2["userid"] = SystemUser.CurrentUser.MQUserID;
* newDr2["status"] = 0;
* newDr2["adduser"] = SystemUser.CurrentUser.UserID;
* newDr2["addtime"] = ti;
* newDr2["updateuser"] = SystemUser.CurrentUser.UserID;
* newDr2["updatetime"] = ti;
* dt_oper.Rows.Add(newDr2);
* dbc.InsertTable(dt_oper);*/
LogBySourcegoodsinfoOffer(dbc, offerid, "市场部询价单已提交企业", SystemUser.CurrentUser.UserName + "在" + ti + "市场部询价单已提交企业", ti);
}
dbc.CommitTransaction();
return(true);
}
return(false);
}
catch (Exception ex)
{
dbc.RoolbackTransaction();
throw ex;
}
}
}
public bool updateOffer(string offerid, JSReader jsr)
{
using (MySqlDbConnection dbc = MySqlConnstr.GetDBConnection())
{
try
{
dbc.BeginTransaction();
String up = "";
String estimatemoney = jsr["estimatemoney"].ToString();
String estimatecarriertype = jsr["estimatecarriertype"].ToString();
String istakegoodsbyestimate = jsr["istakegoodsbyestimate"].ToString();
String estimatetakegoodsmoney = jsr["estimatetakegoodsmoney"].ToString();
if (!string.IsNullOrEmpty(estimatetakegoodsmoney))
{
up += "estimatetakegoodsmoney=" + dbc.ToSqlValue(estimatetakegoodsmoney) + ",";
}
String isvotebyestimate = jsr["isvotebyestimate"].ToString();
String estimatevotemoney = jsr["estimatevotemoney"].ToString();
if (!string.IsNullOrEmpty(estimatevotemoney))
{
up += "estimatevotemoney=" + dbc.ToSqlValue(estimatevotemoney) + ",";
}
String isoilbyestimate = jsr["isoilbyestimate"].ToString();
String estimateoilmoney = jsr["estimateoilmoney"].ToString();
if (!string.IsNullOrEmpty(estimateoilmoney))
{
up += "estimateoilmoney=" + dbc.ToSqlValue(estimateoilmoney) + ",";
}
if (!string.IsNullOrEmpty(up))
{
up = ", " + up;
}
string sql = "update tb_b_sourcegoodsinfo_offer set operatorid=" + dbc.ToSqlValue(SystemUser.CurrentUser.MQUserID) + ",flowstatus = 20,estimatemoney=" + dbc.ToSqlValue(estimatemoney) + ",estimatecarriertype=" + dbc.ToSqlValue(estimatecarriertype) + ",istakegoodsbyestimate=" + dbc.ToSqlValue(istakegoodsbyestimate) + ",isvotebyestimate=" + dbc.ToSqlValue(isvotebyestimate) + ",isoilbyestimate=" + dbc.ToSqlValue(isoilbyestimate) + up.TrimEnd(',') + @"
where offerid=" + dbc.ToSqlValue(offerid);
dbc.ExecuteNonQuery(sql);
sql = "update tb_b_shippingnoteinfo set operatorid = " + dbc.ToSqlValue(SystemUser.CurrentUser.UserID);
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"] = 20;
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;
}
}
}