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.shippingnoteadddatetim>=" + dbc.ToSqlValue(stime);
}
if (etime != null && etime != "")
{
where += " and c.shippingnoteadddatetim<=" + 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 void ConnectTest()
{
DbConnection dbConnection = new MySqlDbConnection(MockData.LHost, MockData.LPort, MockData.LDb, MockData.LUser, MockData.LPass);
Assert.IsFalse(dbConnection.IsAlive);
Assert.AreEqual(System.Data.ConnectionState.Closed, dbConnection.MyCurrentState);
dbConnection.Connect();
Assert.IsTrue(dbConnection.IsAlive);
Assert.AreEqual(System.Data.ConnectionState.Open, dbConnection.MyCurrentState);
}
private IDbContextTransaction BeginTransactionWithNoPreconditions(IsolationLevel isolationLevel)
{
Check.NotNull(_logger, nameof(_logger));
_logger.LogDebug(
RelationalEventId.BeginningTransaction,
isolationLevel,
il => RelationalStrings.RelationalLoggerBeginningTransaction(il.ToString("G")));
// ReSharper disable once AssignNullToNotNullAttribute
CurrentTransaction = new MySqlRelationalTransaction(this, MySqlDbConnection.BeginTransaction(isolationLevel) as MySqlTransaction, _logger, true);
return(CurrentTransaction);
}
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 static int AddMessage(ChatMessage model)
{
using (MySqlDbConnection con = MySqlDbConnection.Connect())
{
con.CommandText = "insert into chatmessage (Mid, UserId, Message, CreateDate) values (@Mid, @UserId, @Message, @CreateDate)";
con.AddParameters("Mid", model.Mid);
con.AddParameters("UserId", model.UserId);
con.AddParameters("Message", model.Message);
con.AddParameters("CreateDate", model.CreateDate);
return con.Exec();
}
}
private void DBTestConnect()
{
bool pass = false;
using (MySqlDbConnection _dbCon = new MySqlDbConnection())
{
if (_dbCon.CanConnect)
{
pass = true;
}
}
_conTestResult = pass;
}
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 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 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;
}
}
}
private async Task <IDbContextTransaction> BeginTransactionWithNoPreconditionsAsync(
IsolationLevel isolationLevel,
CancellationToken cancellationToken = default(CancellationToken)
)
{
Check.NotNull(_logger, nameof(_logger));
_logger.LogDebug(
RelationalEventId.BeginningTransaction,
isolationLevel,
il => RelationalStrings.RelationalLoggerBeginningTransaction(il.ToString("G")));
CurrentTransaction = new MySqlRelationalTransaction(this, await MySqlDbConnection.BeginTransactionAsync(isolationLevel, cancellationToken).ConfigureAwait(false), _logger, true);
return(CurrentTransaction);
}
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 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 void LogBySourcegoodsinfoOffer(MySqlDbConnection dbc, string offerid, string recordtype, string recordmemo, DateTime ti)
{
var dt = dbc.GetEmptyDataTable("tb_b_sourcegoodsinfo_offer_record");
var dr = dt.NewRow();
dr["id"] = Guid.NewGuid();
dr["offerid"] = offerid;
dr["recordtype"] = recordtype;
dr["recordmemo"] = recordmemo;
dr["status"] = 0;
dr["adduser"] = SystemUser.CurrentUser.UserID;
dr["addtime"] = ti;
dr["updateuser"] = SystemUser.CurrentUser.UserID;
dr["updatetime"] = ti;
dt.Rows.Add(dr);
dbc.InsertTable(dt);
}
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 IAVSCDatabase GetIAVSCDatabase
(
DatabaseType databaseType,
string serverName,
string username,
string password,
string databaseName
)
{
IAVSCDatabase db;
switch (databaseType)
{
case DatabaseType.Postgres:
{
db = new PostgresDbConnection(
serverName, username, password, databaseName);
}
break;
case DatabaseType.MySql:
{
db = new MySqlDbConnection(
serverName, username, password, databaseName);
}
break;
case DatabaseType.SqlServer:
{
db = new SqlServerDbConnection(
serverName, username, password, databaseName);
}
break;
default:
{
throw new NotSupportedDatabaseException();
}
}
return(db);
}
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.* 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;
}
}
}
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 void UpdateRatingComment(int myRating1, int myRating2, string myComment, int myId, MySqlDbConnection myDbCon)
{
if (myDbCon == null)
{
myDbCon = new MySqlDbConnection();
}
myDbCon.openConnection();
string querySting = "UPDATE file " +
"SET rating1 = @pRating1" +
", rating2 = @pRating2 " +
", comment = @pComment " +
"WHERE id = @pId ";
List <MySqlParameter> sqlparamList = new List <MySqlParameter>();
MySqlParameter param = new MySqlParameter();
param = new MySqlParameter("@pRating1", MySqlDbType.Int32);
param.Value = myRating1;
sqlparamList.Add(param);
param = new MySqlParameter("@pRating2", MySqlDbType.Int32);
param.Value = myRating2;
sqlparamList.Add(param);
param = new MySqlParameter("@pComment", MySqlDbType.VarChar);
param.Value = myComment;
sqlparamList.Add(param);
param = new MySqlParameter("@pId", MySqlDbType.VarChar);
param.Value = myId;
sqlparamList.Add(param);
myDbCon.SetParameter(sqlparamList.ToArray());
myDbCon.execSqlCommand(querySting);
return;
}
public void DbUpdateIsTarget(MovieImportData myData, MySqlDbConnection myDbCon)
{
MySqlDbConnection dbcon;
string sqlcmd = "";
// 引数にコネクションが指定されていた場合は指定されたコネクションを使用
if (myDbCon != null)
{
dbcon = myDbCon;
}
else
{
dbcon = new MySqlDbConnection();
}
sqlcmd = "UPDATE import ";
sqlcmd += "SET is_target = @IsTarget ";
sqlcmd += "WHERE ID = @Id ";
MySqlCommand command = new MySqlCommand(sqlcmd, dbcon.getMySqlConnection());
DataTable dtSaraly = new DataTable();
List <MySqlParameter> listSqlParams = new List <MySqlParameter>();
MySqlParameter sqlparam = new MySqlParameter("@IsTarget", MySqlDbType.Bit);
sqlparam.Value = myData.IsTarget;
listSqlParams.Add(sqlparam);
sqlparam = new MySqlParameter("@Id", MySqlDbType.Int32);
sqlparam.Value = myData.Id;
listSqlParams.Add(sqlparam);
dbcon.SetParameter(listSqlParams.ToArray());
dbcon.execSqlCommand(sqlcmd);
return;
}
public void InsertQueryTest()
{
int count = 0;
List <string> dbFields = MockData.dbFields;
DbConnection dbConnection = new MySqlDbConnection(MockData.LHost, MockData.LPort, MockData.LDb, MockData.LUser, MockData.LPass);
Assert.IsFalse(dbConnection.IsAlive);
int result = 0;
while (result == 0 && count <= 10)
{
string[] testExecutionValues = MockData.testExecutionValues;
string query = string.Format("INSERT IGNORE Into firmtrail.fills (`{0}`) VALUES ('{1}');", string.Join("`,`", dbFields), string.Format(string.Join("','", testExecutionValues), count));
result = dbConnection.RunNonQuery(query);
count++;
}
Assert.AreNotEqual(0, result);
}
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 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 void DbDelete(MovieImportData myData, MySqlDbConnection myDbCon)
{
MySqlDbConnection dbcon;
string sqlcmd = "";
// 引数にコネクションが指定されていた場合は指定されたコネクションを使用
if (myDbCon != null)
{
dbcon = myDbCon;
}
else
{
dbcon = new MySqlDbConnection();
}
dbcon.openConnection();
sqlcmd = "DELETE FROM import WHERE ID = @Id ";
MySqlCommand command = new MySqlCommand(sqlcmd, dbcon.getMySqlConnection());
DataTable dtSaraly = new DataTable();
List <MySqlParameter> listSqlParams = new List <MySqlParameter>();
MySqlParameter sqlparam = new MySqlParameter("@Id", MySqlDbType.Int32);
sqlparam.Value = myData.Id;
listSqlParams.Add(sqlparam);
dbcon.SetParameter(listSqlParams.ToArray());
command.Parameters.Add(sqlparam);
command.ExecuteNonQuery();
return;
}
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 static IAVSCDatabase GetIAVSCDatabase
(
DatabaseType databaseType,
string connectionString
)
{
IAVSCDatabase db = null;
switch (databaseType)
{
case DatabaseType.Postgres:
{
db = new PostgresDbConnection(connectionString);
}
break;
case DatabaseType.MySql:
{
db = new MySqlDbConnection(connectionString);
}
break;
case DatabaseType.SqlServer:
{
db = new SqlServerDbConnection(connectionString);
}
break;
default:
{
throw new NotSupportedDatabaseException();
}
}
return(db);
}
public List <RecordedData> GetList(MySqlDbConnection myDbCon)
{
List <RecordedData> listData = new List <RecordedData>();
if (myDbCon == null)
{
myDbCon = new MySqlDbConnection();
}
string queryString
= "SELECT r.id "
+ " , r.disk_no, r.seq_no, r.rip_status, r.on_air_date "
+ " , r.time_flag, r.minute, r.channel_no, r.channel_seq "
+ " , p.name, r.detail, r.created_at, r.updated_at "
+ " FROM tv.recorded as r LEFT JOIN tv.program as p "
+ " ON r.channel_no = p.channel_no and r.channel_seq = p.channel_seq "
+ " ORDER BY r.disk_no DESC "
+ "";
MySqlDataReader reader = null;
try
{
reader = myDbCon.GetExecuteReader(queryString);
do
{
if (reader.IsClosed)
{
//_logger.Debug("reader.IsClosed");
throw new Exception("av.storeの取得でreaderがクローズされています");
}
while (reader.Read())
{
RecordedData data = new RecordedData();
int colIdx = 0;
data.Id = MySqlDbExportCommon.GetDbInt(reader, colIdx++);
data.DiskNo = MySqlDbExportCommon.GetDbString(reader, colIdx++);
data.SeqNo = MySqlDbExportCommon.GetDbString(reader, colIdx++);
data.RipStatus = MySqlDbExportCommon.GetDbString(reader, colIdx++);
data.OnAirDate = MySqlDbExportCommon.GetDbDateTime(reader, colIdx++);
data.TimeFlag = MySqlDbExportCommon.GetDbBool(reader, colIdx++);
data.Minute = MySqlDbExportCommon.GetDbInt(reader, colIdx++);
data.ChannelNo = MySqlDbExportCommon.GetDbInt(reader, colIdx++);
data.ChannelSeq = MySqlDbExportCommon.GetDbInt(reader, colIdx++);
data.ProgramName = MySqlDbExportCommon.GetDbString(reader, colIdx++);
data.Detail = MySqlDbExportCommon.GetDbString(reader, colIdx++);
data.CreatedAt = MySqlDbExportCommon.GetDbDateTime(reader, colIdx++);
data.UpdatedAt = MySqlDbExportCommon.GetDbDateTime(reader, colIdx++);
listData.Add(data);
}
} while (reader.NextResult());
}
catch (Exception ex)
{
Debug.Write(ex);
}
finally
{
reader.Close();
}
reader.Close();
myDbCon.closeConnection();
return(listData);
}
