/// <summary>
/// 16
/// </summary>
/// <param name="user"></param>
/// <param name="model"></param>
/// <returns></returns>
public Result Update(UserInfo user, TypeInfo model)
{
using (TransactionScope ts = new TransactionScope())
{
try
{
#region 检测是否存在
string sql = "select count(1) from TypeInfo where typeid = @tid";
Dictionary <string, object> indic = new Dictionary <string, object>();
indic.Add("@tid", model.TypeID);
int count = DbHelper.AccessHelper.ExecuteNonQuery(sql, indic);
if (count == 0)
{
return(new Result()
{
ReturnValue = false, Message = "该类别已删除！"
});
}
#endregion
#region 添加
sql = @" update TypeInfo set BTypeName=@tname where TypeID=@tid
if(@@error=0)
begin
select 1
end
else
begin
select 0
end ";
indic.Add("@tid", model.BTypeName);
count = AccessHelper.ExecuteNonQuery(sql, indic);
if (count == 1)
{
ts.Complete();
return(new Result()
{
ReturnValue = true, Message = "更新成功！"
});
}
else
{
return(new Result()
{
ReturnValue = false, Message = "更新失败！"
});
}
#endregion
}
catch (Exception ex)
{
return(new Result()
{
ReturnValue = false, Message = ex.Message
});
}
}
}
protected string UpdateFuelData(string vin, string status, DataRow drMain, DataTable dtPam, OleDbConnection con)
{
string message = string.Empty;
OleDbTransaction tra = null; //创建事务，开始执行事务
try
{
tra = con.BeginTransaction();
#region 更新基本信息表
string sqlUpdateBasic = @"UPDATE FC_CLJBXX SET
USER_ID=@USER_ID,QCSCQY=@QCSCQY,JKQCZJXS=@JKQCZJXS,CLXH=@CLXH,CLZL=@CLZL,
RLLX=@RLLX,ZCZBZL=@ZCZBZL,ZGCS=@ZGCS,LTGG=@LTGG,ZJ=@ZJ,
TYMC=@TYMC,YYC=@YYC,ZWPS=@ZWPS,ZDSJZZL=@ZDSJZZL,EDZK=@EDZK,LJ=@LJ,
QDXS=@QDXS,JYJGMC=@JYJGMC,JYBGBH=@JYBGBH,STATUS=@STATUS,UPDATETIME=@UPDATETIME,
HGSPBM=@HGSPBM,QTXX=@QTXX
WHERE VIN=@VIN";
OleDbParameter upTime = new OleDbParameter("@UPDATETIME", DateTime.Now);
upTime.OleDbType = OleDbType.DBDate;
OleDbParameter[] parameters =
{
new OleDbParameter("@USER_ID", Utils.localUserId),
new OleDbParameter("@QCSCQY", drMain["QCSCQY"].ToString()),
new OleDbParameter("@JKQCZJXS", drMain["JKQCZJXS"].ToString()),
new OleDbParameter("@CLXH", drMain["CLXH"].ToString()),
new OleDbParameter("@CLZL", drMain["CLZL"].ToString()),
new OleDbParameter("@RLLX", drMain["RLLX"].ToString()),
new OleDbParameter("@ZCZBZL", drMain["ZCZBZL"].ToString()),
new OleDbParameter("@ZGCS", drMain["ZGCS"].ToString()),
new OleDbParameter("@LTGG", drMain["LTGG"].ToString()),
new OleDbParameter("@ZJ", drMain["ZJ"].ToString()),
new OleDbParameter("@TYMC", drMain["TYMC"].ToString()),
new OleDbParameter("@YYC", drMain["YYC"].ToString()),
new OleDbParameter("@ZWPS", drMain["ZWPS"].ToString()),
new OleDbParameter("@ZDSJZZL", drMain["ZDSJZZL"].ToString()),
new OleDbParameter("@EDZK", drMain["EDZK"].ToString()),
new OleDbParameter("@LJ", drMain["LJ"].ToString()),
new OleDbParameter("@QDXS", drMain["QDXS"].ToString()),
new OleDbParameter("@JYJGMC", drMain["JYJGMC"].ToString()),
new OleDbParameter("@JYBGBH", drMain["JYBGBH"].ToString()),
new OleDbParameter("@STATUS", status),
upTime,
new OleDbParameter("@HGSPBM", drMain["HGSPBM"].ToString()),
new OleDbParameter("@QTXX", drMain["CT_QTXX"].ToString()),
new OleDbParameter("@VIN", vin)
};
AccessHelper.ExecuteNonQuery(tra, sqlUpdateBasic, parameters);
#endregion
#region 插入参数信息
// 更新燃料参数表
foreach (DataRow drParam in dtPam.Rows)
{
string paramCode = drParam["PARAM_CODE"].ToString().Trim();
string sqlUpdateParam = @"UPDATE RLLX_PARAM_ENTITY
SET PARAM_VALUE='" + drMain[paramCode] + "' WHERE VIN='" + vin + "' AND PARAM_CODE='" + paramCode + "'";
//OleDbParameter[] paramList = {
// new OleDbParameter("@PARAM_CODE",paramCode),
// new OleDbParameter("@VIN",vin),
// new OleDbParameter("@PARAM_VALUE",drMain[paramCode]),
// };
AccessHelper.ExecuteNonQuery(tra, sqlUpdateParam, null);
}
tra.Commit();
#endregion
}
catch (Exception ex)
{
tra.Rollback();
message = ex.Message + "\r\n";
}
return(message);
}
// 保存SQL
private void SaveClxhParam()
{
using (OleDbConnection conn = new OleDbConnection(AccessHelper.conn))
{
conn.Open();
try
{
//车型燃料参数
string fuelParamName = string.Empty;
StringBuilder fuelParamValue = new StringBuilder();
foreach (Control c in this.tlp.Controls)
{
if (c is TextEdit || c is DevExpress.XtraEditors.ComboBoxEdit)
{
fuelParamName += "," + Convert.ToString(c.Name).Trim();
fuelParamValue.Append("','" + Convert.ToString(c.Text).Trim());
}
}
fuelParamValue.Append("'");
//车型基本信息
StringBuilder basicParamValue = new StringBuilder();
basicParamValue.Append("'" + this.tbqcscqy.Text.Trim());
basicParamValue.Append("','" + this.tbjkqczjxs.Text.Trim());
basicParamValue.Append("','" + this.tbclxh.Text.Trim());
basicParamValue.Append("','" + this.tbHgspbm.Text.Trim());
basicParamValue.Append("','" + this.cbclzl.Text.Trim());
basicParamValue.Append("','" + this.cbyyc.Text.Trim());
basicParamValue.Append("','" + this.cbqdxs.Text.Trim());
basicParamValue.Append("','" + this.tbzwps.Text.Trim());
basicParamValue.Append("','" + this.tbzczbzl.Text.Trim());
basicParamValue.Append("','" + this.tbzdsjzzl.Text.Trim());
basicParamValue.Append("','" + this.tbzgcs.Text.Trim());
basicParamValue.Append("','" + this.tbedzk.Text.Trim());
basicParamValue.Append("','" + this.tbltgg.Text.Trim());
basicParamValue.Append("','" + this.tblj.Text.Trim());
basicParamValue.Append("','" + this.tbzj.Text.Trim());
basicParamValue.Append("','" + this.cbrllx.Text.Trim());
basicParamValue.Append("','" + this.tbbgbh.Text.Trim());
basicParamValue.Append("','" + this.tbjcjgmc.Text.Trim());
basicParamValue.Append("',#" + Convert.ToDateTime(DateTime.Today, CultureInfo.InvariantCulture));
basicParamValue.Append("#,#" + Convert.ToDateTime(DateTime.Today, CultureInfo.InvariantCulture));
basicParamValue.Append("#");
basicParamValue.Append(String.Format(",'{0}'", this.tbUniqueCode.Text.Trim()));
string tableName = "CTNY_MAIN";
if (this.cbrllx.Text.Trim() == "非插电式混合动力")
{
tableName = "FCDS_MAIN";
}
else if (this.cbrllx.Text.Trim() == "插电式混合动力")
{
tableName = "CDS_MAIN";
}
else if (this.cbrllx.Text.Trim() == "纯电动")
{
tableName = "CDD_MAIN";
}
else if (this.cbrllx.Text.Trim() == "燃料电池")
{
tableName = "RLDC_MAIN";
}
// 保存车型基本信息
string sqlStr = string.Format(@"INSERT INTO {0}
({1}{2}) VALUES({3}{4})", tableName, (string)@"QCSCQY,JKQCZJXS,CLXH,HGSPBM,CLZL,YYC,QDXS,ZWPS,ZCZBZL,ZDSJZZL,
ZGCS,EDZK,LTGG,LJ,ZJ,RLLX,JYBGBH,JYJGMC,CREATETIME,UPDATETIME,UNIQUE_CODE", fuelParamName, basicParamValue, fuelParamValue.ToString().Substring(1));
AccessHelper.ExecuteNonQuery(conn, sqlStr, null);
}
catch (Exception ex)
{
throw ex;
}
}
}
// 保存
private void saveParam()
{
string strCon = AccessHelper.conn;
OleDbConnection con = new OleDbConnection(strCon);
con.Open();
OleDbTransaction tra = con.BeginTransaction(); //创建事务，开始执行事务
try
{
string strCreater = Utils.userId;
string strBah = this.tbbah.Text.Trim().ToUpper();
string sqlJbxx = "DELETE FROM FC_CLJBXX WHERE VIN = '" + strBah + "' AND STATUS='1'";
string sqlParam = "DELETE FROM RLLX_PARAM_ENTITY WHERE VIN ='" + strBah + "'";
AccessHelper.ExecuteNonQuery(tra, sqlJbxx, null);
AccessHelper.ExecuteNonQuery(tra, sqlParam, null);
ArrayList sqlList = new ArrayList();
#region 遍历参数,保存
foreach (Control c in this.tlp.Controls)
{
if (c is TextEdit || c is DevExpress.XtraEditors.ComboBoxEdit)
{
//Control[] lblc = clj.Controls.Find("lbl" + c.Name, true);
string paramCode = c.Name;
string paramValue = c.Text;
string strSQL = @"INSERT INTO RLLX_PARAM_ENTITY (PARAM_CODE,VIN,PARAM_VALUE,V_ID)
VALUES(@PARAM_CODE,@VIN,@PARAM_VALUE,@V_ID)";
OleDbParameter[] paramList =
{
new OleDbParameter("@PARAM_CODE", paramCode),
new OleDbParameter("@VIN", strBah),
new OleDbParameter("@PARAM_VALUE", paramValue),
new OleDbParameter("@V_ID", "")
};
AccessHelper.ExecuteNonQuery(tra, strSQL, paramList);
}
}
#endregion
#region 保存车辆基本信息
// 保存车辆基本信息
string sqlStr = @"INSERT INTO FC_CLJBXX
( VIN,
HGSPBM,
USER_ID,
QCSCQY,
JKQCZJXS,
CLXH,
CLZL,
RLLX,
ZCZBZL,
ZGCS,
LTGG,
ZJ,
CLZZRQ,
UPLOADDEADLINE,
TYMC,
YYC,
ZWPS,
ZDSJZZL,
EDZK,
LJ,
QDXS,
STATUS,
JYJGMC,
JYBGBH,
QTXX,
CREATETIME,
UPDATETIME
) VALUES
( @VIN,
@HGSPBM,
@USER_ID,
@QCSCQY,
@JKQCZJXS,
@CLXH,
@CLZL,
@RLLX,
@ZCZBZL,
@ZGCS,
@LTGG,
@ZJ,
@CLZZRQ,
@UPLOADDEADLINE,
@TYMC,
@YYC,
@ZWPS,
@ZDSJZZL,
@EDZK,
@LJ,
@QDXS,
@STATUS,
@JYJGMC,
@JYBGBH,
@QTXX,
@CREATETIME,
@UPDATETIME)";
DateTime clzzrqDate = DateTime.Parse(this.dtclzzrq.Text.Trim());
OleDbParameter clzzrq = new OleDbParameter("@CLZZRQ", clzzrqDate);
clzzrq.OleDbType = OleDbType.DBDate;
DateTime uploadDeadlineDate = Utils.QueryUploadDeadLine(clzzrqDate);
OleDbParameter uploadDeadline = new OleDbParameter("@UPLOADDEADLINE", uploadDeadlineDate);
uploadDeadline.OleDbType = OleDbType.DBDate;
OleDbParameter creTime = new OleDbParameter("@CREATETIME", DateTime.Now);
creTime.OleDbType = OleDbType.DBDate;
OleDbParameter upTime = new OleDbParameter("@UPDATETIME", DateTime.Now);
upTime.OleDbType = OleDbType.DBDate;
OleDbParameter[] param =
{
new OleDbParameter("@VIN", this.tbbah.Text.Trim().ToUpper()),
new OleDbParameter("@HGSPBM", this.tbHgspbm.Text.Trim().ToUpper()),
new OleDbParameter("@USER_ID", strCreater),
new OleDbParameter("@QCSCQY", this.tbqcscqy.Text.Trim()),
new OleDbParameter("@JKQCZJXS", this.tbjkqczjxs.Text.Trim()),
new OleDbParameter("@CLXH", this.tbclxh.Text.Trim()),
new OleDbParameter("@CLZL", this.cbclzl.Text.Trim()),
new OleDbParameter("@RLLX", this.cbrllx.Text.Trim()),
new OleDbParameter("@ZCZBZL", this.tbzczbzl.Text.Trim()),
new OleDbParameter("@ZGCS", this.tbzgcs.Text.Trim()),
new OleDbParameter("@LTGG", this.tbltgg.Text.Trim()),
new OleDbParameter("@ZJ", this.tbzj.Text.Trim()),
clzzrq,
uploadDeadline,
new OleDbParameter("@TYMC", this.tbtymc.Text.Trim()),
new OleDbParameter("@YYC", this.cbyyc.Text.Trim()),
new OleDbParameter("@ZWPS", this.tbzwps.Text.Trim()),
new OleDbParameter("@ZDSJZZL", this.tbzdsjzzl.Text.Trim()),
new OleDbParameter("@EDZK", this.tbedzk.Text.Trim()),
new OleDbParameter("@LJ", this.tblj.Text.Trim()),
new OleDbParameter("@QDXS", this.cbqdxs.Text.Trim()),
new OleDbParameter("@STATUS", "1"),
new OleDbParameter("@JYJGMC", this.tbjcjgmc.Text.Trim()),
new OleDbParameter("@JYBGBH", this.tbbgbh.Text.Trim()),
new OleDbParameter("@QTXX", this.tbQtxx.Text.Trim()),
creTime,
upTime
};
#endregion
AccessHelper.ExecuteNonQuery(tra, sqlStr, param);
tra.Commit();
//MessageBox.Show("保存成功!");
strVin = strBah; //备案号
//this.Close();
}
catch (Exception ex)
{
// MessageBox.Show("保存失败!");
tra.Rollback();
throw ex;
}
finally
{
con.Close();
}
}
private void btnCopy_Click(object sender, EventArgs e)
{
string newVin = tbCopyVin.Text.Trim().ToUpper();
if (newVin == "")
{
MessageBox.Show("请输入备案号！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
if (IsVinExit(newVin))
{
MessageBox.Show(string.Format("VIN:{0}数据已经录入", newVin), "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
char bi;
DataCheck dc = new DataCheck();
if (!dc.CheckCLSBDH(this.tbCopyVin.Text.Trim().ToUpper(), out bi))
{
if (bi == '-')
{
MessageBox.Show("请核对【备案号(VIN)】为17位字母或者数字!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
else
{
MessageBox.Show("【备案号(VIN)】校验失败！第9位应为:'" + bi + "'", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
return;
}
OleDbConnection conn = new OleDbConnection(AccessHelper.conn);
conn.Open();
OleDbTransaction ts = conn.BeginTransaction();
try
{
string jbxxsql = @"INSERT INTO FC_CLJBXX
(V_ID,VIN,HGSPBM,USER_ID,QCSCQY,JKQCZJXS,CLXH,CLZL,RLLX,ZCZBZL,
ZGCS,LTGG,ZJ,CLZZRQ,UPLOADDEADLINE,TYMC,YYC,ZWPS,ZDSJZZL,EDZK,LJ,
QDXS,STATUS,JYJGMC,JYBGBH,QTXX,CREATETIME,UPDATETIME)
SELECT
NULL AS V_ID,@VIN AS VIN,t.HGSPBM,t.USER_ID,t.QCSCQY,t.JKQCZJXS,t.CLXH,t.CLZL,t.RLLX,t.ZCZBZL,
t.ZGCS,t.LTGG,t.ZJ,t.CLZZRQ,t.UPLOADDEADLINE,t.TYMC,t.YYC,t.ZWPS,t.ZDSJZZL,t.EDZK,t.LJ,
t.QDXS,'1' AS STATUS,t.JYJGMC,t.JYBGBH ,t.QTXX,@CREATETIME AS CREATETIME,@UPDATETIME AS UPDATETIME
FROM FC_CLJBXX t WHERE t.VIN=@TEMP_VIN";
OleDbParameter creTime = new OleDbParameter("@CREATETIME", DateTime.Now);
creTime.OleDbType = OleDbType.DBDate;
OleDbParameter upTime = new OleDbParameter("@UPDATETIME", DateTime.Now);
upTime.OleDbType = OleDbType.DBDate;
OleDbParameter[] param =
{
new OleDbParameter("@VIN", tbCopyVin.Text.Trim().ToUpper()),
creTime,
upTime,
new OleDbParameter("@TEMP_VIN", tempVin.ToUpper())
};
// 复制基本信息
int jbxxcount = AccessHelper.ExecuteNonQuery(ts, jbxxsql, param);
string entitysql = @"INSERT INTO RLLX_PARAM_ENTITY (PARAM_CODE,VIN,PARAM_VALUE,V_ID)
SELECT
PARAM_CODE,@VIN,PARAM_VALUE,NULL AS V_ID
FROM RLLX_PARAM_ENTITY WHERE VIN=@TEMP_VIN";
OleDbParameter[] eneityparam =
{
new OleDbParameter("@VIN", tbCopyVin.Text.Trim().ToUpper()),
new OleDbParameter("@TEMP_VIN", tempVin.ToUpper())
};
// 复制参数信息
int entitycount = AccessHelper.ExecuteNonQuery(ts, entitysql, eneityparam);
ts.Commit();
MessageBox.Show("复制成功！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
ts.Rollback();
MessageBox.Show("复制失败：" + ex.Message, "异常", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
conn.Close();
}
this.Close();
}
private bool InsertFC_CLJBXX(DataView dv, string flag, string rllxParam)
{
bool result = false;
using (OleDbConnection con = new OleDbConnection(AccessHelper.conn))
{
con.Open();
OleDbTransaction tra = null; //创建事务，开始执行事务
try
{
tra = con.BeginTransaction();
foreach (DataRow drMain in dv.Table.Rows)
{
#region 待生成的燃料基本信息数据存入燃料基本信息表
string vin = drMain["VIN"].ToString().Trim();
string sqlDeleteBasic = "DELETE FROM FC_CLJBXX WHERE VIN='" + vin + "'";
AccessHelper.ExecuteNonQuery(tra, sqlDeleteBasic, null);
string sqlInsertBasic = @"INSERT INTO FC_CLJBXX
( VIN,USER_ID,QCSCQY,JKQCZJXS,CLZZRQ,UPLOADDEADLINE,CLXH,CLZL,
RLLX,ZCZBZL,ZGCS,LTGG,ZJ,
TYMC,YYC,ZWPS,ZDSJZZL,EDZK,LJ,
QDXS,JYJGMC,JYBGBH,HGSPBM,QTXX,STATUS,CREATETIME,UPDATETIME
) VALUES
( @VIN,@USER_ID,@QCSCQY,@JKQCZJXS,@CLZZRQ,@UPLOADDEADLINE,@CLXH,@CLZL,
@RLLX,@ZCZBZL,@ZGCS,@LTGG,@ZJ,
@TYMC,@YYC,@ZWPS,@ZDSJZZL,@EDZK,@LJ,
@QDXS,@JYJGMC,@JYBGBH,@HGSPBM,@QTXX,@STATUS,@CREATETIME,@UPDATETIME)";
DateTime clzzrqDate;
try
{
clzzrqDate = DateTime.ParseExact(drMain["CLZZRQ"].ToString().Trim(), "yyyyMMdd", System.Globalization.CultureInfo.CurrentCulture);
}
catch (Exception)
{
clzzrqDate = Convert.ToDateTime(drMain["CLZZRQ"]);
}
//DateTime clzzrqDate = Convert.ToDateTime(drVin["CLZZRQ"].ToString().Trim(), CultureInfo.InvariantCulture);
OleDbParameter clzzrq = new OleDbParameter("@CLZZRQ", clzzrqDate);
clzzrq.OleDbType = OleDbType.DBDate;
DateTime uploadDeadlineDate = miutils.QueryUploadDeadLine(clzzrqDate);
OleDbParameter uploadDeadline = new OleDbParameter("@UPLOADDEADLINE", uploadDeadlineDate);
uploadDeadline.OleDbType = OleDbType.DBDate;
OleDbParameter creTime = new OleDbParameter("@CREATETIME", DateTime.Now);
creTime.OleDbType = OleDbType.DBDate;
OleDbParameter upTime = new OleDbParameter("@UPDATETIME", DateTime.Now);
upTime.OleDbType = OleDbType.DBDate;
string qtxx = string.Empty;
if (dv.Table.Columns.Contains("CT_QTXX"))
{
qtxx = drMain["CT_QTXX"].ToString().Trim();
}
OleDbParameter[] param =
{
new OleDbParameter("@VIN", drMain["VIN"].ToString().Trim()),
new OleDbParameter("@USER_ID", Utils.userId),
new OleDbParameter("@QCSCQY", drMain["QCSCQY"].ToString().Trim()),
new OleDbParameter("@JKQCZJXS", drMain["JKQCZJXS"].ToString().Trim()),
clzzrq,
uploadDeadline,
new OleDbParameter("@CLXH", drMain["CLXH"].ToString().Trim()),
new OleDbParameter("@CLZL", drMain["CLZL"].ToString().Trim()),
new OleDbParameter("@RLLX", drMain["RLLX"].ToString().Trim()),
new OleDbParameter("@ZCZBZL", drMain["ZCZBZL"].ToString().Trim()),
new OleDbParameter("@ZGCS", drMain["ZGCS"].ToString().Trim()),
new OleDbParameter("@LTGG", drMain["LTGG"].ToString().Trim()),
new OleDbParameter("@ZJ", drMain["ZJ"].ToString().Trim()),
new OleDbParameter("@TYMC", drMain["TYMC"].ToString().Trim()),
new OleDbParameter("@YYC", drMain["YYC"].ToString().Trim()),
new OleDbParameter("@ZWPS", drMain["ZWPS"].ToString().Trim()),
new OleDbParameter("@ZDSJZZL", drMain["ZDSJZZL"].ToString().Trim()),
new OleDbParameter("@EDZK", drMain["EDZK"].ToString().Trim()),
new OleDbParameter("@LJ", drMain["LJ"].ToString().Trim()),
new OleDbParameter("@QDXS", drMain["QDXS"].ToString().Trim()),
new OleDbParameter("@JYJGMC", drMain["JYJGMC"].ToString().Trim()),
new OleDbParameter("@JYBGBH", drMain["JYBGBH"].ToString().Trim()),
new OleDbParameter("@HGSPBM", drMain["HGSPBM"].ToString().Trim()),
new OleDbParameter("@QTXX", qtxx),
// 状态为9表示数据以导入，但未被激活，此时用来供用户修改
new OleDbParameter("@STATUS", flag),
creTime,
upTime
};
AccessHelper.ExecuteNonQuery(tra, sqlInsertBasic, param);
#endregion
#region 插入参数信息
string sqlDelParam = "DELETE FROM RLLX_PARAM_ENTITY WHERE VIN ='" + drMain["VIN"].ToString().Trim() + "'";
AccessHelper.ExecuteNonQuery(tra, sqlDelParam, null);
var rows = dtCtnyPam.Select("FUEL_TYPE='" + rllxParam + "' and STATUS='1'");
// 待生成的燃料参数信息存入燃料参数表
foreach (DataRow drParam in rows)
{
string paramCode = drParam["PARAM_CODE"].ToString().Trim();
string sqlInsertParam = @"INSERT INTO RLLX_PARAM_ENTITY
(PARAM_CODE,VIN,PARAM_VALUE,V_ID)
VALUES
(@PARAM_CODE,@VIN,@PARAM_VALUE,@V_ID)";
OleDbParameter[] paramList =
{
new OleDbParameter("@PARAM_CODE", paramCode),
new OleDbParameter("@VIN", drMain["VIN"].ToString().Trim()),
new OleDbParameter("@PARAM_VALUE", drMain[paramCode].ToString().Trim()),
new OleDbParameter("@V_ID", "")
};
AccessHelper.ExecuteNonQuery(tra, sqlInsertParam, paramList);
}
#endregion
}
tra.Commit();
result = true;
}
catch (Exception ex)
{
tra.Rollback();
throw ex;
}
finally
{
tra.Dispose();
con.Close();
}
}
return(result);
}
private void btnDeleteMain_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
{
var gridControl = (GridControl)this.btnFcdsSearch.SelectedTabPage.Controls[0];
gridControl.MainView.PostEditor();
DataView dv = (DataView)gridControl.MainView.DataSource;
if (dv == null)
{
MessageBox.Show("请选择车型数据！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
var result = from DataRow dr in dv.Table.Rows
where (bool)dr["check"] == true
select dr["UNIQUE_CODE"];
string selectedParamEntityIds = "";
if (result != null)
{
foreach (var a in result)
{
selectedParamEntityIds += ",'" + a + "'";
}
}
if ("" == selectedParamEntityIds)
{
MessageBox.Show("请选择要删除的数据！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
if (MessageBox.Show("确定要删除吗？", "删除确认", MessageBoxButtons.OKCancel, MessageBoxIcon.Question, MessageBoxDefaultButton.Button2) != DialogResult.OK)
{
return;
}
selectedParamEntityIds = selectedParamEntityIds.Substring(1);
var tableName = string.Empty;
if (gridControl.Name.Equals("dgvCtny"))
{
tableName = "CTNY";
}
else if (gridControl.Name.Equals("dgvFcds"))
{
tableName = "FCDS";
}
else if (gridControl.Name.Equals("dgvCds"))
{
tableName = "CDS";
}
else if (gridControl.Name.Equals("dgvCdd"))
{
tableName = "CDD";
}
else if (gridControl.Name.Equals("dgvRldc"))
{
tableName = "RLDC";
}
string sql = @"delete * from " + tableName + "_MAIN where UNIQUE_CODE in (" + selectedParamEntityIds + ")";
int i = AccessHelper.ExecuteNonQuery(AccessHelper.conn, sql, null);
this.SearchMainData(tableName);
}
// 保存SQL
private void SaveClxhParam()
{
string strCon = AccessHelper.conn;
OleDbConnection con = new OleDbConnection(strCon);
con.Open();
OleDbTransaction tra = con.BeginTransaction(); //创建事务，开始执行事务
try
{
string strCreater = Utils.userId;
#region 保存车型基本参数
string basicParamName = @"QCSCQY,JKQCZJXS,CLXH,HGSPBM,CLZL,YYC,QDXS,ZWPS,ZCZBZL,ZDSJZZL,
ZGCS,EDZK,LTGG,LJ,ZJ,RLLX,JYBGBH,JYJGMC,CREATETIME,UPDATETIME";
StringBuilder basicParamValue = new StringBuilder();
basicParamValue.Append("'" + this.tbqcscqy.Text.Trim());
basicParamValue.Append("','" + this.tbjkqczjxs.Text.Trim());
basicParamValue.Append("','" + this.tbclxh.Text.Trim());
basicParamValue.Append("','" + this.tbHgspbm.Text.Trim());
basicParamValue.Append("','" + this.cbclzl.Text.Trim());
basicParamValue.Append("','" + this.cbyyc.Text.Trim());
basicParamValue.Append("','" + this.cbqdxs.Text.Trim());
basicParamValue.Append("','" + this.tbzwps.Text.Trim());
basicParamValue.Append("','" + this.tbzczbzl.Text.Trim());
basicParamValue.Append("','" + this.tbzdsjzzl.Text.Trim());
basicParamValue.Append("','" + this.tbzgcs.Text.Trim());
basicParamValue.Append("','" + this.tbedzk.Text.Trim());
basicParamValue.Append("','" + this.tbltgg.Text.Trim());
basicParamValue.Append("','" + this.tblj.Text.Trim());
basicParamValue.Append("','" + this.tbzj.Text.Trim());
basicParamValue.Append("','" + this.cbrllx.Text.Trim());
basicParamValue.Append("','" + this.tbbgbh.Text.Trim());
basicParamValue.Append("','" + this.tbjcjgmc.Text.Trim());
basicParamValue.Append("',#" + Convert.ToDateTime(DateTime.Today, CultureInfo.InvariantCulture));
basicParamValue.Append("#,#" + Convert.ToDateTime(DateTime.Today, CultureInfo.InvariantCulture));
basicParamValue.Append("#");
string fuelParamName = string.Empty;
StringBuilder fuelParamValue = new StringBuilder();
// 遍历子参数
foreach (Control c in this.tlp.Controls)
{
if (c is TextEdit || c is DevExpress.XtraEditors.ComboBoxEdit)
{
//Control[] lblc = clj.Controls.Find("lbl" + c.Name, true);
fuelParamName += "," + Convert.ToString(c.Name).Trim();
fuelParamValue.Append("','" + Convert.ToString(c.Text).Trim());
}
}
fuelParamValue.Append("'");
// 保存车辆基本信息
string sqlStr = string.Format(@"INSERT INTO CTNY_MAIN
({0}{1}) VALUES({2}{3})", basicParamName, fuelParamName, basicParamValue.ToString(), fuelParamValue.ToString().Substring(1));
AccessHelper.ExecuteNonQuery(tra, sqlStr, null);
#endregion
tra.Commit();
}
catch (Exception ex)
{
tra.Rollback();
throw ex;
}
finally
{
con.Close();
}
}
/// <summary>
/// 修改配置
/// </summary>
/// <param name="dt"></param>
/// <returns></returns>
public int UpdateConfig()
{
int rows = 0;
try
{
string strCon = XMLHelper.strGetConnectString();
OleDbConnection Conn = new OleDbConnection(strCon);
Conn.Open();
OleDbCommand command = new OleDbCommand("select * from setupconfig", Conn);
OleDbDataReader reader = command.ExecuteReader();
DataTable dtsql = new DataTable();
dtsql = reader.GetSchemaTable();
Conn.Close();
Conn.Dispose();
AccessHelper ah = new AccessHelper();
if (dtsql != null && dtsql.Rows[11]["NumericPrecision"].ToString() != "15")
{
string strsql = "alter table setupconfig alter column HKD double";
ah.ExecuteSQLNonquery(strsql);
strsql = "alter table setupconfig alter column USD double";
ah.ExecuteSQLNonquery(strsql);
strsql = "alter table setupconfig alter column CNY double";
ah.ExecuteSQLNonquery(strsql);
strsql = "alter table setupconfig alter column EUR double";
ah.ExecuteSQLNonquery(strsql);
strsql = "alter table setupconfig alter column GBP double";
ah.ExecuteSQLNonquery(strsql);
strsql = "alter table setupconfig alter column JPY double";
ah.ExecuteSQLNonquery(strsql);
strsql = "alter table setupconfig alter column MOP double";
ah.ExecuteSQLNonquery(strsql);
strsql = "alter table setupconfig alter column MYR double";
ah.ExecuteSQLNonquery(strsql);
strsql = "alter table setupconfig alter column SGD double";
ah.ExecuteSQLNonquery(strsql);
strsql = "alter table setupconfig alter column TWD double";
ah.ExecuteSQLNonquery(strsql);
strsql = "alter table setupconfig alter column USrate double";
ah.ExecuteSQLNonquery(strsql);
strsql = "alter table setupconfig alter column HKrate double";
ah.ExecuteSQLNonquery(strsql);
strsql = "alter table setupconfig alter column EURrate double";
ah.ExecuteSQLNonquery(strsql);
strsql = "alter table setupconfig alter column GBPrate double";
ah.ExecuteSQLNonquery(strsql);
strsql = "alter table setupconfig alter column JPYrate double";
ah.ExecuteSQLNonquery(strsql);
strsql = "alter table setupconfig alter column MOPrate double";
ah.ExecuteSQLNonquery(strsql);
strsql = "alter table setupconfig alter column MYRrate double";
ah.ExecuteSQLNonquery(strsql);
strsql = "alter table setupconfig alter column SGDrate double";
ah.ExecuteSQLNonquery(strsql);
strsql = "alter table setupconfig alter column TWDrate double";
ah.ExecuteSQLNonquery(strsql);
}
//SqlQueue sq = new SqlQueue();
StringBuilder insertSql = new StringBuilder();
insertSql.Append("Update [SetupConfig] set ");
insertSql.AppendFormat(" [EmailID]='{0}',", EmailID);
insertSql.AppendFormat(" [EmailPwd]='{0}',", EmailPwd);
insertSql.AppendFormat(" [EmailAddress]='{0}',", EmailAddress);
insertSql.AppendFormat(" [EmailSMTP]='{0}',", EmailSMTP);
insertSql.AppendFormat(" [HKD]={0},", HKD);
insertSql.AppendFormat(" [USD]={0},", USD);
insertSql.AppendFormat(" [CNY]={0},", CNY);
insertSql.AppendFormat(" [EUR]={0},", EUR);
insertSql.AppendFormat(" [GBP]={0},", GBP);
insertSql.AppendFormat(" [JPY]={0},", JPY);
insertSql.AppendFormat(" [MOP]={0},", MOP);
insertSql.AppendFormat(" [MYR]={0},", MYR);
insertSql.AppendFormat(" [SGD]={0},", SGD);
insertSql.AppendFormat(" [TWD]={0},", TWD);
insertSql.AppendFormat(" [USrate]={0},", USrate);
insertSql.AppendFormat(" [HKrate]={0},", HKrate);
insertSql.AppendFormat(" [EURrate]={0},", EURrate);
insertSql.AppendFormat(" [GBPrate]={0},", GBPrate);
insertSql.AppendFormat(" [JPYrate]={0},", JPYrate);
insertSql.AppendFormat(" [MOPrate]={0},", MOPrate);
insertSql.AppendFormat(" [MYRrate]={0},", MYRrate);
insertSql.AppendFormat(" [SGDrate]={0},", SGDrate);
insertSql.AppendFormat(" [TWDrate]={0}", TWDrate);
//insertSql.AppendFormat(" where [ID]={0}", ID);
//sq.InsertQuery(insertSql.ToString(), "", "", 0, 0);
//sq.Close();
ah.ExecuteNonQuery(insertSql.ToString());
ah.Close();
return(1);
}
catch (Exception ex)
{
//throw ex;
return(0);
}
}
/// <summary>
///
/// </summary>
private void UpdateQRCode2AliCloud()
{
//取得所有的缓存数据
DataTable dtAllData = new DataTable();
dtAllData = AccessHelper.ExecuteDataTable(connAccess, "SELECT [FCodeID],[FQRCode], [FBillNoEntryId] FROM T_QRCODE");
if (dtAllData.Rows.Count > 0)
{
//得到唯一的单据行号
string[] DistinctBillNo = CommonProcess.GetDistinctBillNo(dtAllData, "FBillNoEntryId").Split(';');
if (DistinctBillNo.Length > 0)
{
//对每一行进行处理
foreach (string billNo in DistinctBillNo)
{
//过滤只留下一行数据
DataTable dtFilteredData = CommonProcess.FilterData(dtAllData, "FBillNoEntryId = '" + billNo + "'");
if (dtFilteredData.Rows.Count > 0)
{
//生成插入语句
sql = BuildDetailSql(dtFilteredData);
//写入云
int ret = SqlHelper.ExecuteNonQuery(connALiClouds, sql);
//如果返回值等于一行数据的个数，表示该行数据已经到云
if (ret == dtFilteredData.Rows.Count)
{
// 更新主表中已扫个数
string billNoEntryID = dtFilteredData.Rows[0]["FBillNoEntryId"].ToString();
int retUpdateQty = UpdateCloudQty(billNoEntryID, ret);
//更新成功
if (retUpdateQty > 0)
{
//删除ACCESS数据库中的该行数据
sql = string.Format("Delete from t_QRCode where FBillNoEntryId = '{0}'", billNoEntryID);
int retInt = AccessHelper.ExecuteNonQuery(connAccess, sql);
if (retInt == ret)
{
//删除成功，则是上传成功
CustomDesktopAlert.H2("数据上传成功！");
}
else
{
//删除失败，则是上传失败
CustomDesktopAlert.H2("数据未能全部上传！");
}
}
else
{
//更新个数失败
CustomDesktopAlert.H2("更新主表失败！");
}
}
else
{
CustomDesktopAlert.H2("缓存中无数据");
}
}
else
{
CustomDesktopAlert.H2("保存数据出错！");
}
}
}
else
{
CustomDesktopAlert.H2("缓存中的单号列表为空");
}
}
else
{
CustomDesktopAlert.H2("缓存中无数据");
}
}
private void toolStripButton10_Click(object sender, EventArgs e)
{
double douDefBon = 100000.00;
DateTime dtBeginDate = DateTime.Parse(DateTime.Now.Year.ToString() + "-2-1");
DateTime dtEndDate = DateTime.Parse(DateTime.Now.ToShortDateString());
//double douCurrUsed;
string strDebug = "--";
try
{
#region init all user's bonefit
strDebug = "init...";
//string strInitSQL = "update Users set UsedAmount=0,RestAmount=" + douDefBon;
AccessHelper ah = new AccessHelper();
//ah.ExecuteSQLNonquery(strInitSQL);
//ah.Close();
string strMaxEmpDate = DateTime.Now.AddDays(-180).ToShortDateString();
string strBeginDate = DateTime.Now.Year.ToString() + "/2/1";
string strEndDate = DateTime.Parse(DateTime.Now.Year.ToString() + "/08/01 00:00:00").AddDays(-1).ToShortDateString();
string strBeginDate2 = DateTime.Now.Year.ToString() + "/8/1";
string strEndDate2 = DateTime.Parse(DateTime.Now.AddYears(1).Year.ToString() + "/02/01 00:00:00").AddDays(-1).ToShortDateString();
string strSQL = "update Users set UsedAmount = 0, RestAmount = 50000 where EmpDate <=#" + strMaxEmpDate + "# and EmpDate < #" + strBeginDate + "# ";
ah.ExecuteNonQuery(strSQL);
Thread.Sleep(3000);
ah.Close();
strSQL = "update Users set UsedAmount = 0, RestAmount = 50000 * (datediff('d', EmpDate, #" + strEndDate + "#) / 180) where EmpDate <=#" + strMaxEmpDate + "# and EmpDate >= #" + strBeginDate + "# ";
ah.ExecuteNonQuery(strSQL);
Thread.Sleep(3000);
ah.Close();
strSQL = "update Users set UsedAmount = 0, RestAmount = 0 where EmpDate >#" + strMaxEmpDate + "#";
ah.ExecuteNonQuery(strSQL);
Thread.Sleep(3000);
ah.Close();
if (DateTime.Now.Month >= 8 || DateTime.Now.Month == 1)
{
strSQL = "update Users set RestAmount = RestAmount + 50000 where EmpDate <=#" + strMaxEmpDate + "# and EmpDate < #" + strBeginDate2 + "# ";
ah.ExecuteNonQuery(strSQL);
Thread.Sleep(3000);
ah.Close();
strSQL = "update Users set UsedAmount = 0, RestAmount = RestAmount + (50000 * (datediff('d', EmpDate, #" + strEndDate + "#) / 180)) where EmpDate <=#" + strMaxEmpDate + "# and EmpDate >=#" + strBeginDate2 + "#";
ah.ExecuteNonQuery(strSQL);
Thread.Sleep(3000);
ah.Close();
//strSQL = "update Users set UsedAmount = 0, RestAmount = 50000 * (datediff('d', EmpDate, #" + strEndDate2 + "#) / 180) where EmpDate =#" + strMaxEmpDate + "# and EmpDate >= #" + strBeginDate2 + "#";
//ah.ExecuteNonQuery(strSQL);
//ah.Close();
strSQL = "update Users set UsedAmount = 0, RestAmount = 0 where EmpDate >#" + strMaxEmpDate + "#";
ah.ExecuteNonQuery(strSQL);
Thread.Sleep(3000);
ah.Close();
}
#endregion
strDebug = "Get All Data...";
string strSelectSQL = "select * from Users where IsDelete=0 and IsAble=1";
AccessHelper ah2 = new AccessHelper();
DataTable dtSelectSQL = ah2.SelectToDataTable(strSelectSQL);
ah2.Close();
foreach (DataRow dr in dtSelectSQL.Rows)
{
double douUsed = 0.00;
string UID = dr["UID"].ToString();
douDefBon = double.Parse(dr["RestAmount"].ToString());
strDebug = "Get Price from " + UID + " ";
string strGetAllSQL = "select * from ApplicationInfo where IsDelete=0 and SalesDate>=#" + dtBeginDate.ToShortDateString() + "# and SalesDate<#" + DateTime.Now.AddDays(1) + "# and Applicants='" + UID + "' ";
AccessHelper ahIN = new AccessHelper();
DataTable dtIN = ahIN.SelectToDataTable(strGetAllSQL);
ahIN.Close();
for (int i = 0; i < dtIN.Rows.Count; i++)
{
double douTotalPrice = 0.00;
string strGetDetail = "select * from ApplicationDetail where TransNo = '" + dtIN.Rows[i]["TransNo"].ToString() + "'";
AccessHelper ahGetDetail = new AccessHelper();
DataTable dtDetail = ahGetDetail.SelectToDataTable(strGetDetail);
ahGetDetail.Close();
for (int ii = 0; ii < dtDetail.Rows.Count; ii++)
{
if (dtDetail.Rows[ii]["IsSuccess"].ToString() == "1")
{
decimal MoneyDiscont = 1;
switch (dtDetail.Rows[ii]["MoneyUnit"].ToString())
{
case "1":
default:
MoneyDiscont = 1;
break;
case "2":
MoneyDiscont = EmailControl.config.USrate;
break;
case "3":
MoneyDiscont = EmailControl.config.HKrate;
break;
case "4":
MoneyDiscont = EmailControl.config.MOPrate;
break;
case "5":
MoneyDiscont = EmailControl.config.SGDrate;
break;
case "6":
MoneyDiscont = EmailControl.config.MYRrate;
break;
case "7":
MoneyDiscont = EmailControl.config.GBPrate;
break;
case "8":
MoneyDiscont = EmailControl.config.EURrate;
break;
case "9":
MoneyDiscont = EmailControl.config.JPYrate;
break;
case "10":
MoneyDiscont = EmailControl.config.TWDrate;
break;
}
if (!CheckSpecial(dtDetail.Rows[ii]["ItemID"].ToString()) && int.Parse(dtIN.Rows[i]["AppState"].ToString()) >= 6)
{
douUsed += double.Parse(dtDetail.Rows[ii]["FinalPrice"].ToString()) * double.Parse(MoneyDiscont.ToString());
}
if (int.Parse(dtDetail.Rows[ii]["IsRepetition"].ToString()) == 1)
{
douTotalPrice += double.Parse(dtDetail.Rows[ii]["FinalPrice"].ToString()) * double.Parse(MoneyDiscont.ToString());
}
}
}
string strUpdate = "update ApplicationInfo set TotalPrice = " + douTotalPrice + " where TransNo = '" + dtIN.Rows[i]["TransNo"].ToString() + "'";
AccessHelper ahup = new AccessHelper();
ahup.ExecuteSQLNonquery(strUpdate);
Thread.Sleep(500);
ahup.Close();
douTotalPrice = 0.00;
}
strDebug = "Update data to " + UID + " ";
double douRest = douDefBon - douUsed;
string strUpdateSQL = "update Users set UsedAmount=" + douUsed + ",RestAmount=" + douRest + " where UID='" + UID + "' ";
AccessHelper ahRunRest = new AccessHelper();
ahRunRest.ExecuteSQLNonquery(strUpdateSQL);
Thread.Sleep(500);
ahRunRest.Close();
//if (dtIN != null && dtIN.Rows.Count>0)
//{
// if(dtIN.Rows[0][0].ToString() != null && dtIN.Rows[0][0].ToString() != "")
// {
// douCurrUsed = double.Parse(dtIN.Rows[0][0].ToString());
// }
// else
// {
// douCurrUsed = 0.0;
// }
// if(douCurrUsed > 0)
// {
// strDebug = "Update data to " + UID + " ";
// double douRest = douDefBon - douCurrUsed;
// string strUpdateSQL = "update Users set UsedAmount=" + douCurrUsed + ",RestAmount=" + douRest + " where UID='" + UID + "' ";
// AccessHelper ahRunRest = new AccessHelper();
// ahRunRest.ExecuteSQLNonquery(strUpdateSQL);
// ahRunRest.Close();
// //System.Threading.Thread.Sleep(500);
// }
//}
}
MessageBox.Show("重新计算成功!", "通知!", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
catch (Exception ex)
{
MessageBox.Show("重新计算失败::" + strDebug + "::" + ex.Message, "警告!", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}