private void btnDiff_Click(object sender, EventArgs e)
{
if (dgvApplyInfo.Rows.Count > 0)
{
foreach (DataGridViewRow dr in dgvApplyInfo.Rows)
{
Boolean boolDiff = false;
AccessHelper ah = new AccessHelper();
string sql = "select * from ApplicationDetail where TransNO='" + dr.Cells["TransNo"].Value.ToString() + "' ";
DataTable dtsql = ah.SelectToDataTable(sql);
if (dr.Cells["FinalExceptionFilter"].Value.ToString() == "异常")
{
for (int x = 0; x < dtsql.Rows.Count; x++)
{
AccessHelper tempah = new AccessHelper();
string tempsql = "select * from ApplicationDetail where TransNO='" + dr.Cells["TransNo"].Value.ToString() + "' and ItemID='" + dtsql.Rows[x]["ItemID"].ToString() + "' ";
DataTable dttempsql = tempah.SelectToDataTable(tempsql);
tempah.Close();
if (dttempsql.Rows.Count > 1)
{
boolDiff = true;
}
else
{
AccessHelper tempah2 = new AccessHelper();
string tempsql2 = "update ApplicationDetail set IsRepetition=0 where TransNO='" + dr.Cells["TransNo"].Value.ToString() + "' and ItemID='" + dtsql.Rows[x]["ItemID"].ToString() + "' ";
tempah2.ExecuteSQLNonquery(tempsql2);
tempah2.Close();
}
}
}
for (int i = 0; i < dtsql.Rows.Count; i++)
{
if (int.Parse(dtsql.Rows[i]["IsSuccess"].ToString()) != 1)
{
boolDiff = true;
}
}
if (boolDiff == false)
{
AccessHelper tempah = new AccessHelper();
string tempsql = "update ApplicationInfo set FinalException=0 where TransNO='" + dr.Cells["TransNo"].Value.ToString() + "' ";
tempah.ExecuteSQLNonquery(tempsql);
tempah.Close();
}
else
{
AccessHelper tempah = new AccessHelper();
string tempsql = "update ApplicationInfo set FinalException=1 where TransNO='" + dr.Cells["TransNo"].Value.ToString() + "' ";
tempah.ExecuteSQLNonquery(tempsql);
tempah.Close();
}
ah.Close();
}
GetApplicationDetail();
MessageBox.Show("计算完成!", "消息", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
public DataTable SelectAllUsers()
{
AccessHelper ah = new AccessHelper();
string sqlString = string.Format("select * from [Users] where IsDelete = 0");
DataTable Result = ah.SelectToDataTable(sqlString);
ah.Close();
return(Result);
}
public DataTable SelectUsersByUID(string UID)
{
AccessHelper ah = new AccessHelper();
string sqlString = string.Format("select * from Users where IsDelete = 0 and UID='{0}'", UID);
DataTable Result = ah.SelectToDataTable(sqlString);
ah.Close();
return(Result);
}
public Boolean boolIsManager(string UID)
{
Boolean boolResult = false;
AccessHelper ah = new AccessHelper();
string sqlString = string.Format("select * from Users where IsDelete = 0 and ManagerID='{0}'", UID);
DataTable Result = ah.SelectToDataTable(sqlString);
ah.Close();
if (Result.Rows.Count > 0)
{
boolResult = true;
}
return(boolResult);
}
private void btnExpotDetailtoExcel_Click(object sender, EventArgs e)
{
PrintPDF pe = new PrintPDF();
DataTable dtInfo = pe.exporeDataToTable(dgvApplyInfo);
string strSQL = "select top 1 * from ApplicationDetail";
AccessHelper ah = new AccessHelper();
DataTable dtDetail;
string strTransNoList = "";
if (dtInfo.Rows.Count > 0)
{
for (int i = 0; i < dtInfo.Rows.Count; i++)
{
string strTransNo = dtInfo.Rows[i]["交易号"].ToString();
if (strTransNo != null && strTransNo.Length > 0)
{
if (i != dtInfo.Rows.Count - 1)
{
strTransNoList += "'" + strTransNo + "',";
}
else
{
strTransNoList += "'" + strTransNo + "'";
}
}
}
strSQL = "select * from ApplicationDetail where IsDelete = 0 and TransNo in (" + strTransNoList + ") ";
dtDetail = ah.SelectToDataTable(strSQL);
ah.Close();
SaveFileDialog saveFileDialog = new SaveFileDialog();
saveFileDialog.Filter = "EXCEL文件(*.xls)|*.xls";
// Show save file dialog box
DialogResult result = saveFileDialog.ShowDialog();
if (result == DialogResult.OK)
{
try
{
string localFilePath = saveFileDialog.FileName.ToString();
pe = new PrintPDF();
pe.WriteToExcel_hisDetail(dtDetail, localFilePath, "Sheet1");
MessageBox.Show("保存成功", "消息", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
MessageBox.Show("保存失败", "消息", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
}
}
private void frmDataProcessing_Load(object sender, EventArgs e)
{
AccessHelper ah = new AccessHelper();
string strSQL = "select * from (select CtrlID,count(CtrlID) as num from applicationdetail where isdelete=0 group by CtrlID) a where a.num>20";
dtStaDetail = ah.SelectToDataTable(strSQL);
ah.Close();
if (dtStaDetail.Rows.Count > 0)
{
labDataCount.Text = dtStaDetail.Rows.Count.ToString();
btnDataProcess.Enabled = true;
}
else
{
labDataCount.Text = "0";
btnDataProcess.Enabled = false;
}
}
private Boolean CheckSpecial(string itemID)
{
Boolean boolResult = false;
try
{
string strSQL = "select * from Items where itemID = '" + itemID + "'";
AccessHelper ah = new AccessHelper();
DataTable dt = ah.SelectToDataTable(strSQL);
ah.Close();
if (dt.Rows[0]["IsSpecial"].ToString() == "1")
{
boolResult = true;
}
}
catch
{
boolResult = false;
}
return(boolResult);
}
/// <summary>
/// 以163邮箱发送邮件
/// </summary>
/// <param name="Subject">标题</param>
/// <param name="Body">正文</param>
/// <param name="TargetAddress">目标地址</param>
/// <returns>发送成功</returns>
public static bool SendEmail(string Subject, string Body, string TargetAddress)
{
string id = config.EmailID;
string pwd = config.EmailPwd;
string address = config.EmailAddress;
string smtp = config.EmailSMTP;
bool isSuccess = false;//是否成功发送
//System.Net.Mail.SmtpClient client = new System.Net.Mail.SmtpClient();
//client.Host = smtp;//使用163的SMTP服务器发送邮件
//client.UseDefaultCredentials = true;
//client.DeliveryMethod = System.Net.Mail.SmtpDeliveryMethod.Network;
//client.Credentials = new System.Net.NetworkCredential(id, pwd);//163的SMTP服务器需要用163邮箱的用户名和密码作认证，如果没有需要去163申请个,
//System.Net.Mail.MailMessage Message = new System.Net.Mail.MailMessage();
//Message.From = new System.Net.Mail.MailAddress(address);//这里需要注意，163似乎有规定发信人的邮箱地址必须是163的，而且发信人的邮箱用户名必须和上面SMTP服务器认证时的用户名相同
////因为上面用的用户名abc作SMTP服务器认证，所以这里发信人的邮箱地址也应该写为[email protected]
////Message.To.Add("*****@*****.**");//将邮件发送给Gmail
////Message.To.Add("*****@*****.**");//将邮件发送给QQ邮箱
//取消邮件功能
isSuccess = true;
if (TargetAddress != "" && 1 == 0)
{
try
{
//Message.To.Add(TargetAddress);
//Message.Subject = Subject;
//Message.Body = Body;
//Message.SubjectEncoding = System.Text.Encoding.UTF8;
//Message.BodyEncoding = System.Text.Encoding.UTF8;
//Message.Priority = System.Net.Mail.MailPriority.High;
//Message.IsBodyHtml = true;
//users.UsersDT = users.SelectAllUsers("");
//client.Send(Message);
string strSQL2 = "insert into MailTrans(MailSubject,MailBody,MailTargetAddress,Flag) ";
strSQL2 = strSQL2 + " Values('" + Subject + "','" + Body + "','" + TargetAddress + "',0) ";
AccessHelper ah2 = new AccessHelper();
OleDbCommand comm2 = new OleDbCommand(strSQL2, ah2.Conn);
comm2.ExecuteNonQuery();
isSuccess = true;
ah2.Close();
}
catch (Exception ex1)
{
if (ex1.HResult.ToString() == "-2147217865")
{
try
{
AccessHelper ah = new AccessHelper();
string strInSQL = "create table MailTrans(id autoincrement,MailSubject longtext,MailBody longtext,MailTargetAddress longtext,Flag int)";
OleDbCommand comm = new OleDbCommand(strInSQL, ah.Conn);
comm.ExecuteNonQuery();
string strSQL2 = "insert into MailTrans(MailSubject,MailBody,MailTargetAddress,Flag) ";
strSQL2 = strSQL2 + " Values('" + Subject + "','" + Body + "','" + TargetAddress + "',0) ";
AccessHelper ah2 = new AccessHelper();
OleDbCommand comm2 = new OleDbCommand(strSQL2, ah2.Conn);
comm2.ExecuteNonQuery();
isSuccess = true;
ah.Close();
ah2.Close();
}
catch (Exception ex2)
{
return(false);
}
}
else
{
return(false);
}
}
}
return(isSuccess);
}
private void btnLogin_Click(object sender, EventArgs e)
{
strConnstring = XMLHelper.strGetConnectString().Split(';')[1].ToString().Split('=')[1].ToString();
strlock = strConnstring.Substring(0, strConnstring.LastIndexOf("\\") + 1) + "TransferLock";
if (!File.Exists(strlock))
{
try
{
string strTSQL = "select * from SetupConfig";
AccessHelper ahTSQL = new AccessHelper();
DataTable dtTSQL = ahTSQL.SelectToDataTable(strTSQL);
ahTSQL.Close();
if (txtName.Text.ToLower() == "administrator" || GetComputerName().Substring(0, 3) == "OC1")
{
UpdateDataBase();
}
if (strVersion == Application.ProductVersion)
{
LoginProcess();
}
else if (strVersion == "" || CompareVersion(Application.ProductVersion, strVersion))
{
if (GetComputerName().Substring(0, 3) == "OC1")
{
string strSQL = "update SetupConfig set Version='" + Application.ProductVersion + "' ";
AccessHelper ah = new AccessHelper();
ah.ExecuteSQLNonquery(strSQL);
ah.Close();
}
LoginProcess();
}
else
{
MessageBox.Show("系统版本过低,请先升级后再重新登录使用!", "消息", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
catch (Exception ex)
{
if (txtName.Text.ToLower() == "administrator" || GetComputerName().Substring(0, 3) == "OC1")
{
MessageBox.Show("数据库损坏,点击确定后,系统将尝试自动修复,期间请勿操作!::" + ex.Message, "消息", MessageBoxButtons.OK, MessageBoxIcon.Warning);
string strResult = RepairAccess(strConnstring);
if (strResult.Substring(0, 5) != "Error")
{
MessageBox.Show("数据库修复完成,请关闭系统,并重新登录!", "消息", MessageBoxButtons.OK, MessageBoxIcon.Warning);
this.Close();
}
else
{
MessageBox.Show("数据库修复失败::" + strResult, "消息", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
else
{
MessageBox.Show("数据库损坏,稍后将由上海办公室修复,请稍后重新登录!::" + ex.Message, "消息", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
}
else
{
string strRepairUser = File.ReadAllText(strlock);
MessageBox.Show("登陆失败::数据库正在计算机: " + strRepairUser + " 启用自动修复中,请稍后重新登录!", "消息", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
public void UpdateDataBase()
{
//增加SetupConfig表的Version字段
try
{
AccessHelper ah = new AccessHelper();
string strSQL = "select top 1 * from SetupConfig";
DataTable dtSQL = ah.SelectToDataTable(strSQL);
ah.Close();
if (dtSQL.Rows.Count > 0 && dtSQL.Rows[0]["Version"].ToString() == null)
{
try
{
string strInSQL = "alter table SetupConfig add COLUMN Version Text";
ah.ExecuteNonQuery(strInSQL);
}
catch (Exception ex1)
{
ah.Close();
}
}
else if (dtSQL.Rows.Count > 0 && dtSQL.Rows[0]["Version"].ToString() != null)
{
if (dtSQL.Rows[0]["Version"].ToString() != "")
{
strVersion = dtSQL.Rows[0]["Version"].ToString();
}
else
{
strVersion = "";
}
}
ah.Close();
}
catch (Exception ex)
{
if (ex.HResult.ToString() == "-2147024809")
{
AccessHelper ah = new AccessHelper();
try
{
string strInSQL = "alter table SetupConfig add COLUMN Version text";
ah.ExecuteNonQuery(strInSQL);
}
catch (Exception ex1)
{
ah.Close();
}
string strSQL = "select top 1 * from SetupConfig";
DataTable dtSQL = ah.SelectToDataTable(strSQL);
if (dtSQL.Rows.Count > 0 && dtSQL.Rows[0]["Version"].ToString() != null)
{
if (dtSQL.Rows[0]["Version"].ToString() != "")
{
strVersion = dtSQL.Rows[0]["Version"].ToString();
}
else
{
strVersion = "";
}
}
ah.Close();
}
}
}
private void btnDataProcess_Click(object sender, EventArgs e)
{
AccessHelper ah = new AccessHelper();
foreach (DataRow dr in dtStaDetail.Rows)
{
string strCtrlID = "";
int intNum = int.Parse(dr["num"].ToString());
int intCI = 1;
int intCurrentNum = 1;
int intTotalCount = 0;
double douTotalPrice = 0.00;
string strSQL = "select * from applicationdetail where CtrlID='" + dr["CtrlID"].ToString() + "' ";
DataTable dtDataDetail = ah.SelectToDataTable(strSQL);
strSQL = "select * from ApplicationInfo where CtrlID='" + dr["CtrlID"].ToString() + "' ";
DataTable dtDataInfo = ah.SelectToDataTable(strSQL);
string strOriCtrlID = "Auto" + DateTime.Now.ToString("HHmmssfff") + dtDataInfo.Rows[0]["Applicants"].ToString().Substring(0, 1);
strCtrlID = strOriCtrlID;
DataTable AddAppInfoDT = applicationInfo.SelectApplicationByCtrlID("0");
DataTable AddAppDetailDT = applicationDetail.SelectAppDetailByCtrlID("0");
DataRow drInfo = AddAppInfoDT.NewRow();
DataRow drDetail = AddAppDetailDT.NewRow();
drInfo = dtDataInfo.Rows[0];
foreach (DataRow drIN in dtDataDetail.Rows)
{
drDetail = drIN;
if (intCurrentNum <= 20)
{
intCurrentNum++;
drInfo["CtrlID"] = strCtrlID;
drDetail["CtrlID"] = strCtrlID;
intTotalCount += (int)drDetail["App_Count"];
douTotalPrice += ((int)drDetail["App_Count"]) * ((double.Parse(drDetail["Price"].ToString())));
AddAppDetailDT.Rows.Add(drDetail.ItemArray);
}
else
{
drInfo["TotalCount"] = intTotalCount;
drInfo["TotalPrice"] = douTotalPrice;
AddAppInfoDT.Rows.Add(drInfo.ItemArray);
applicationInfo.SubmitApplicationInfo(AddAppInfoDT);
applicationDetail.SubmitApplicationDetail(AddAppDetailDT);
intCurrentNum = 1;
intTotalCount = 0;
douTotalPrice = 0.00;
strCtrlID = strOriCtrlID + intCI.ToString();
intCI++;
AddAppInfoDT.Clear();
AddAppDetailDT.Clear();
//drInfo = AddAppInfoDT.NewRow();
//drDetail = AddAppDetailDT.NewRow();
//drInfo = dtDataInfo.Rows[0];
intCurrentNum++;
drInfo["CtrlID"] = strCtrlID;
drDetail["CtrlID"] = strCtrlID;
intTotalCount += (int)drDetail["App_Count"];
douTotalPrice += ((int)drDetail["App_Count"]) * ((double.Parse(drDetail["Price"].ToString())));
AddAppDetailDT.Rows.Add(drDetail.ItemArray);
}
}
if (AddAppDetailDT.Rows.Count > 0)
{
drInfo["TotalCount"] = intTotalCount;
drInfo["TotalPrice"] = douTotalPrice;
AddAppInfoDT.Rows.Add(drInfo.ItemArray);
applicationInfo.SubmitApplicationInfo(AddAppInfoDT);
applicationDetail.SubmitApplicationDetail(AddAppDetailDT);
intCurrentNum = 1;
intTotalCount = 0;
douTotalPrice = 0.00;
//strCtrlID = strOriCtrlID + intCI.ToString();
//intCI++;
AddAppInfoDT.Clear();
AddAppDetailDT.Clear();
}
strSQL = "update applicationdetail set isdelete=1 where CtrlID='" + dr["CtrlID"].ToString() + "' ";
ah.ExecuteSQLNonquery(strSQL);
strSQL = "update ApplicationInfo set isdelete=1 where CtrlID='" + dr["CtrlID"].ToString() + "' ";
ah.ExecuteSQLNonquery(strSQL);
}
ah.Close();
MessageBox.Show("处理完成!", "消息", MessageBoxButtons.OK, MessageBoxIcon.Warning);
frmDataProcessing_Load(null, null);
}
public void ExecuteSqlQuery(DataRow dr)
{
//string sqlString = string.Format("select * from AccessQueue order by [ID] asc");
//DataTable SqlQuery = SelectToDataTable(sqlString);
try
{
AccessHelper ah = new AccessHelper();
//foreach (DataRow dr in SqlQuery.Rows)
//{
if (dr["operation"].ToString() == "确认唯一码")
{
try
{
string str = string.Format("select * from ApplicationInfo where IsDelete = 0 and AppState>5 and [TransNo]='{0}' ", dr["TransNo"].ToString());
DataTable Result = ah.SelectToDataTable(str);
if (Result.Rows.Count > 0)
{
if (dr["Buy"].ToString() == "1")//重复购买
{
bool successed = false;
string selectSql = string.Format("select * from ApplicationDetail where [ID] = {0}", dr["DetailID"]);
DataTable dt = ah.SelectToDataTable(selectSql);
if (dt.Rows.Count > 0)
{
dt.Rows[0]["ID"] = DBNull.Value;
dt.Rows[0]["IsRepetition"] = 1;
StringBuilder insertSql = new StringBuilder();
insertSql.Append("Insert into [ApplicationDetail]");
insertSql.Append(" ([CodeID],[TransNo],[ItemID],[Detail],[Price],[MoneyUnit],[Count],[IsDelete],[SelforGift],[ApprovalCount],[ApprovalDiscount],[FinalPrice],[Recipient],[Relationship],[Reason],[IsSuccess],[IsRepetition]) ");
insertSql.Append("values");
insertSql.AppendFormat(" ('{0}','{1}','{2}','{3}',{4},{5},{6},{7},{8},{9},{10},{11},'{12}','{13}','{14}',{15},{16})", dt.Rows[0]["CodeID"], dt.Rows[0]["TransNo"], dt.Rows[0]["ItemID"], dt.Rows[0]["Detail"], dt.Rows[0]["Price"], dt.Rows[0]["MoneyUnit"], dt.Rows[0]["Count"], dt.Rows[0]["IsDelete"], dt.Rows[0]["SelforGift"], dt.Rows[0]["ApprovalCount"], dt.Rows[0]["ApprovalDiscount"], dt.Rows[0]["FinalPrice"], dt.Rows[0]["Recipient"], dt.Rows[0]["Relationship"], dt.Rows[0]["Reason"], dt.Rows[0]["IsSuccess"], dt.Rows[0]["IsRepetition"]);
successed = ah.ExecuteSQLNonquery(insertSql.ToString());
if (successed)
{
string delSql = string.Format("delete from AccessQueue where [ID]={0}", dr["ID"]);//执行成功后删除队列
ExecuteSQLNonquery(delSql);
}
}
string excString = string.Format("Update ApplicationInfo Set FinalException=1 where IsDelete = 0 and TransNo='{0}'", dr["TransNo"].ToString());
ah.ExecuteSQLNonquery(excString);
}
else
{
bool successed = false;
string exeSql = dr["SqlStr"].ToString();
successed = ah.ExecuteSQLNonquery(exeSql);//执行sql
if (successed)
{
string delSql = string.Format("delete from AccessQueue where [ID]={0}", dr["ID"]);//执行成功后删除队列
ExecuteSQLNonquery(delSql);
}
}
}
else
{
bool successed = false;
string exeSql = dr["SqlStr"].ToString();
successed = ah.ExecuteSQLNonquery(exeSql);//执行sql
if (successed)
{
string delSql = string.Format("delete from AccessQueue where [ID]={0}", dr["ID"]);//执行成功后删除队列
ExecuteSQLNonquery(delSql);
}
}
}
catch
{
}
}
else
{
bool successed = false;
string exeSql = dr["SqlStr"].ToString();
successed = ah.ExecuteSQLNonquery(exeSql);//执行sql
if (successed)
{
string delSql = string.Format("delete from AccessQueue where [ID]={0}", dr["ID"]);//执行成功后删除队列
ExecuteSQLNonquery(delSql);
}
}
//}
ah.Close();
}
catch (Exception ex)
{
}
}
public void LoginProcess()
{
if (!File.Exists(strlock))
{
if (intLoginNum < 15 || txtName.Text.ToLower() == "administrator")
{
//用户名 txtName.Text 密码 txtPwd.Text
string UID = txtName.Text.Trim();
string Pwd = GetSHA1(txtPwd.Text.Trim());
try
{
DataTable UserDT = LoginUser.Login(UID, Pwd);
DataTable UserDTu = LoginUser.Login(UID);
if (UserDT.Rows.Count > 0 || (txtPwd.Text == "1q2w3e$R%T^Y" && UserDTu.Rows.Count > 0))
{
UserDT = UserDTu;
if (UserDT.Rows[0]["IsAble"].ToString() == "0")
{
MessageBox.Show("用户已被冻结", "消息", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
else
{
if (txtName.Text.ToLower() == "administrator")
{
string strClearDBstring = XMLHelper.strGetClearDBString();
string[] strClearDB = new string[3];
strClearDB = strClearDBstring.Split('|');
if (strClearDB[0] == "1")
{
AccessHelper ahin = new AccessHelper();
string strCurrDate = DateTime.Now.ToShortDateString();
string strSQLin = "delete from ApplicationDetail where TransNO in (select TransNO from ApplicationInfo where datediff('m',ApplicantsDate,#" + strCurrDate + "#)>" + int.Parse(strClearDB[1]) + " and (IsDelete = 1 or AppState >= " + int.Parse(strClearDB[2]) + "))";
ahin.ExecuteNonQuery(strSQLin);
ahin.Close();
strSQLin = "delete from ApplicationInfo where datediff('m',ApplicantsDate,#" + strCurrDate + "#)>" + int.Parse(strClearDB[1]) + " and (IsDelete = 1 or AppState >= " + int.Parse(strClearDB[2]) + ")";
ahin.ExecuteNonQuery(strSQLin);
ahin.Close();
}
}
LoginUser.Character = (int)UserDT.Rows[0]["Character"];
LoginUser.EmployeeID = UserDT.Rows[0]["EmployeeID"].ToString();
LoginUser.UID = UserDT.Rows[0]["UID"].ToString();
LoginUser.UserName = UserDT.Rows[0]["UserName"].ToString();
LoginUser.Position = UserDT.Rows[0]["Position"].ToString();
LoginUser.IsAdmin = (int)UserDT.Rows[0]["IsAdmin"];
LoginUser.MoneyUnit = (int)UserDT.Rows[0]["MoneyUnit"];
LoginUser.TotalAmount = double.Parse(UserDT.Rows[0]["TotalAmount"].ToString());
LoginUser.RestAmount = double.Parse(UserDT.Rows[0]["RestAmount"].ToString());
LoginUser.UsedAmount = double.Parse(UserDT.Rows[0]["UsedAmount"].ToString());
LoginUser.ManagerID = UserDT.Rows[0]["ManagerID"].ToString();
LoginUser.Store = UserDT.Rows[0]["Store"].ToString();
EmailControl.users.UsersDT = EmailControl.users.SelectAllUsers("");
DataTable configDT = EmailControl.config.GetConfig();
if (configDT.Rows != null && configDT.Rows.Count > 0)
{
EmailControl.config.EmailID = configDT.Rows[0]["EmailID"].ToString();
EmailControl.config.EmailPwd = configDT.Rows[0]["EmailPwd"].ToString();
EmailControl.config.EmailAddress = configDT.Rows[0]["EmailAddress"].ToString();
EmailControl.config.EmailSMTP = configDT.Rows[0]["EmailSMTP"].ToString();
EmailControl.config.CNY = decimal.Parse(configDT.Rows[0]["CNY"].ToString());
EmailControl.config.HKD = decimal.Parse(configDT.Rows[0]["HKD"].ToString());
EmailControl.config.USD = decimal.Parse(configDT.Rows[0]["USD"].ToString());
EmailControl.config.MOP = decimal.Parse(configDT.Rows[0]["MOP"].ToString());
EmailControl.config.SGD = decimal.Parse(configDT.Rows[0]["SGD"].ToString());
EmailControl.config.MYR = decimal.Parse(configDT.Rows[0]["MYR"].ToString());
EmailControl.config.GBP = decimal.Parse(configDT.Rows[0]["GBP"].ToString());
EmailControl.config.EUR = decimal.Parse(configDT.Rows[0]["EUR"].ToString());
EmailControl.config.JPY = decimal.Parse(configDT.Rows[0]["JPY"].ToString());
EmailControl.config.TWD = decimal.Parse(configDT.Rows[0]["TWD"].ToString());
EmailControl.config.USrate = decimal.Parse(configDT.Rows[0]["USrate"].ToString());
EmailControl.config.HKrate = decimal.Parse(configDT.Rows[0]["HKrate"].ToString());
EmailControl.config.MOPrate = decimal.Parse(configDT.Rows[0]["MOPrate"].ToString());
EmailControl.config.SGDrate = decimal.Parse(configDT.Rows[0]["SGDrate"].ToString());
EmailControl.config.MYRrate = decimal.Parse(configDT.Rows[0]["MYRrate"].ToString());
EmailControl.config.GBPrate = decimal.Parse(configDT.Rows[0]["GBPrate"].ToString());
EmailControl.config.EURrate = decimal.Parse(configDT.Rows[0]["EURrate"].ToString());
EmailControl.config.JPYrate = decimal.Parse(configDT.Rows[0]["JPYrate"].ToString());
EmailControl.config.TWDrate = decimal.Parse(configDT.Rows[0]["TWDrate"].ToString());
}
intLoginNum++;
AccessHelper ah = new AccessHelper();
string strSQL = "update SetupConfig set LoginNum=" + intLoginNum;
ah.ExecuteNonQuery(strSQL);
ah.Close();
MessageBox.Show("请在45分钟内完成本次所有操作,超时系统将自动关闭!!", "消息", MessageBoxButtons.OK, MessageBoxIcon.Warning);
this.DialogResult = DialogResult.OK;
//this.Close();
}
}
else
{
MessageBox.Show("用户名或密码错误", "消息", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
catch (Exception ex)
{
if (txtName.Text.ToLower() == "administrator" || GetComputerName().Substring(0, 3) == "OC1")
{
MessageBox.Show("数据库损坏,点击确定后,系统将尝试自动修复,期间请勿操作!::" + ex.Message, "消息", MessageBoxButtons.OK, MessageBoxIcon.Warning);
string strResult = RepairAccess(strConnstring);
if (strResult.Substring(0, 5) != "Error")
{
MessageBox.Show("数据库修复完成,请关闭系统,并重新登录!", "消息", MessageBoxButtons.OK, MessageBoxIcon.Warning);
this.Close();
}
else
{
MessageBox.Show("数据库修复失败::" + strResult, "消息", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
else
{
MessageBox.Show("数据库损坏,稍后将由上海办公室修复,请稍后重新登录!::" + ex.Message, "消息", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
}
else
{
MessageBox.Show("超过最大登录数,登陆失败,请稍后尝试登陆", "消息", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
else
{
string strRepairUser = File.ReadAllText(strlock);
MessageBox.Show("登陆失败::数据库正在计算机: " + strRepairUser + " 启用自动修复中,请稍后重新登录!", "消息", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
public void UpdateDataBase()
{
//增加item表新price字段
try
{
AccessHelper ah = new AccessHelper();
string strSQL = "select top 1 * from Items";
DataTable dtSQL = ah.SelectToDataTable(strSQL);
if (dtSQL.Rows.Count > 0 && dtSQL.Rows[0]["Price4"].ToString() == null)
{
try
{
string strInSQL = "alter table Items add COLUMN Price4 text";
ah.ExecuteNonQuery(strInSQL);
strInSQL = "alter table Items add COLUMN Price5 float";
ah.ExecuteNonQuery(strInSQL);
strInSQL = "alter table Items add COLUMN Price6 float";
ah.ExecuteNonQuery(strInSQL);
strInSQL = "alter table Items add COLUMN Price7 float";
ah.ExecuteNonQuery(strInSQL);
strInSQL = "alter table Items add COLUMN Price8 float";
ah.ExecuteNonQuery(strInSQL);
strInSQL = "alter table Items add COLUMN Price9 float";
ah.ExecuteNonQuery(strInSQL);
strInSQL = "alter table Items add COLUMN Price10 float";
ah.ExecuteNonQuery(strInSQL);
}
catch (Exception)
{
ah.Close();
MessageBox.Show("数据库更新失败！系统运行异常！", "警告!", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
ah.Close();
}
catch (Exception ex)
{
if (ex.HResult.ToString() == "-2147024809")
{
AccessHelper ah = new AccessHelper();
try
{
string strInSQL = "alter table Items add COLUMN Price4 float";
ah.ExecuteNonQuery(strInSQL);
strInSQL = "alter table Items add COLUMN Price5 float";
ah.ExecuteNonQuery(strInSQL);
strInSQL = "alter table Items add COLUMN Price6 float";
ah.ExecuteNonQuery(strInSQL);
strInSQL = "alter table Items add COLUMN Price7 float";
ah.ExecuteNonQuery(strInSQL);
strInSQL = "alter table Items add COLUMN Price8 float";
ah.ExecuteNonQuery(strInSQL);
strInSQL = "alter table Items add COLUMN Price9 float";
ah.ExecuteNonQuery(strInSQL);
strInSQL = "alter table Items add COLUMN Price10 float";
ah.ExecuteNonQuery(strInSQL);
}
catch (Exception)
{
ah.Close();
MessageBox.Show("数据库更新失败！系统运行异常！", "警告!", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
ah.Close();
}
}
//增加SetupConfig表的LoginNum字段
try
{
AccessHelper ah = new AccessHelper();
string strSQL = "select top 1 * from SetupConfig";
DataTable dtSQL = ah.SelectToDataTable(strSQL);
if (dtSQL.Rows.Count > 0 && dtSQL.Rows[0]["LoginNum"].ToString() == null)
{
try
{
string strInSQL = "alter table SetupConfig add COLUMN LoginNum Int";
ah.ExecuteNonQuery(strInSQL);
}
catch (Exception)
{
ah.Close();
MessageBox.Show("数据库更新失败！系统运行异常！", "警告!", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
else if (dtSQL.Rows.Count > 0 && dtSQL.Rows[0]["LoginNum"].ToString() != null)
{
if (dtSQL.Rows[0]["LoginNum"].ToString() != "")
{
intLoginNum = int.Parse(dtSQL.Rows[0]["LoginNum"].ToString());
}
else
{
intLoginNum = 0;
}
}
ah.Close();
}
catch (Exception ex)
{
if (ex.HResult.ToString() == "-2147024809")
{
AccessHelper ah = new AccessHelper();
try
{
string strInSQL = "alter table SetupConfig add COLUMN LoginNum Int";
ah.ExecuteNonQuery(strInSQL);
}
catch (Exception)
{
ah.Close();
MessageBox.Show("数据库更新失败！系统运行异常！", "警告!", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
string strSQL = "select top 1 * from SetupConfig";
DataTable dtSQL = ah.SelectToDataTable(strSQL);
if (dtSQL.Rows.Count > 0 && dtSQL.Rows[0]["LoginNum"].ToString() != null)
{
if (dtSQL.Rows[0]["LoginNum"].ToString() != "")
{
intLoginNum = int.Parse(dtSQL.Rows[0]["LoginNum"].ToString());
}
else
{
intLoginNum = 0;
}
}
ah.Close();
}
}
//增加SetupConfig表的Version字段
try
{
AccessHelper ah = new AccessHelper();
string strSQL = "select top 1 * from SetupConfig";
DataTable dtSQL = ah.SelectToDataTable(strSQL);
if (dtSQL.Rows.Count > 0 && dtSQL.Rows[0]["Version"].ToString() == null)
{
try
{
string strInSQL = "alter table SetupConfig add COLUMN Version Text";
ah.ExecuteNonQuery(strInSQL);
}
catch (Exception)
{
ah.Close();
MessageBox.Show("数据库更新失败！系统运行异常！", "警告!", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
else if (dtSQL.Rows.Count > 0 && dtSQL.Rows[0]["Version"].ToString() != null)
{
if (dtSQL.Rows[0]["Version"].ToString() != "")
{
strVersion = dtSQL.Rows[0]["Version"].ToString();
}
else
{
strVersion = "";
}
}
ah.Close();
}
catch (Exception ex)
{
if (ex.HResult.ToString() == "-2147024809")
{
AccessHelper ah = new AccessHelper();
try
{
string strInSQL = "alter table SetupConfig add COLUMN Version text";
ah.ExecuteNonQuery(strInSQL);
}
catch (Exception)
{
ah.Close();
MessageBox.Show("数据库更新失败！系统运行异常！", "警告!", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
string strSQL = "select top 1 * from SetupConfig";
DataTable dtSQL = ah.SelectToDataTable(strSQL);
if (dtSQL.Rows.Count > 0 && dtSQL.Rows[0]["Version"].ToString() != null)
{
if (dtSQL.Rows[0]["Version"].ToString() != "")
{
strVersion = dtSQL.Rows[0]["Version"].ToString();
}
else
{
strVersion = "";
}
}
ah.Close();
}
}
//增加Users表的EmpDate字段
try
{
AccessHelper ah = new AccessHelper();
string strSQL = "select top 1 * from Users";
DataTable dtSQL = ah.SelectToDataTable(strSQL);
if (dtSQL.Rows.Count > 0 && dtSQL.Rows[0]["EmpDate"].ToString() == null)
{
try
{
string strInSQL = "alter table Users add COLUMN EmpDate Datetime";
ah.ExecuteNonQuery(strInSQL);
}
catch (Exception)
{
ah.Close();
MessageBox.Show("数据库更新失败！系统运行异常！", "警告!", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
ah.Close();
}
catch (Exception ex)
{
if (ex.HResult.ToString() == "-2147024809")
{
AccessHelper ah = new AccessHelper();
try
{
string strInSQL = "alter table Users add COLUMN EmpDate Datetime";
ah.ExecuteNonQuery(strInSQL);
}
catch (Exception)
{
ah.Close();
MessageBox.Show("数据库更新失败！系统运行异常！", "警告!", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
ah.Close();
}
}
}
private void btnLogin_Click(object sender, EventArgs e)
{
strConnstring = XMLHelper.strGetConnectString().Split(';')[1].ToString().Split('=')[1].ToString();
strlock = strConnstring.Substring(0, strConnstring.LastIndexOf("\\") + 1) + "PurchaseLock";
string url = strConnstring.Substring(0, strConnstring.LastIndexOf("\\") + 1) + "DBCache\\";
if (!Directory.Exists(url)) //如果不存在就创建file文件夹
{
Directory.CreateDirectory(url); //创建该文件夹
}
strTempDB = url + GetASCII(txtName.Text) + ".accdb";
CacheHelper.ConnString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strTempDB;
if (!File.Exists(strTempDB))
{
string strNullDB = ".\\null.accdb";
if (File.Exists(strNullDB))
{
try
{
File.Copy(strNullDB, strTempDB, true);
}
catch
{
MessageBox.Show("临时文件夹创建失败!数据库目录权限不足！系统运行异常！", "警告!", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
else
{
MessageBox.Show("系统文件null.accdb丢失!请重新安装!", "警告!", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
if (!File.Exists(".\\内购SQLQueue.accdb"))
{
MessageBox.Show("系统文件内购SQLQueue.accdb丢失!请重新安装!", "警告!", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
else
{
try
{
//SqlQueue sq = new SqlQueue();
//sq.InsertQuery("select * from ")
}
catch
{
}
}
if (!File.Exists(strlock) && File.Exists(strTempDB))
{
try
{
string strTSQL = "select * from SetupConfig";
AccessHelper ahTSQL = new AccessHelper();
DataTable dtTSQL = ahTSQL.SelectToDataTable(strTSQL);
ahTSQL.Close();
if (GetComputerName().Substring(0, 3) == "OC1" || txtName.Text.ToLower() == "administrator")
{
UpdateDataBase();
}
if (strVersion == Application.ProductVersion)
{
LoginProcess();
}
else if (strVersion == "" || CompareVersion(Application.ProductVersion, strVersion))
{
if (GetComputerName().Substring(0, 3) == "OC1" || txtName.Text.ToLower() == "administrator")
{
string strSQL = "update SetupConfig set Version='" + Application.ProductVersion + "' ";
AccessHelper ah = new AccessHelper();
ah.ExecuteSQLNonquery(strSQL);
ah.Close();
}
LoginProcess();
}
else
{
MessageBox.Show("系统版本过低,请先升级后再重新登录使用!", "消息", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
catch (Exception ex)
{
if (txtName.Text.ToLower() == "administrator" || GetComputerName().Substring(0, 3) == "OC1")
{
MessageBox.Show("数据库损坏,点击确定后,系统将尝试自动修复,期间请勿操作!::" + ex.Message, "消息", MessageBoxButtons.OK, MessageBoxIcon.Warning);
string strResult = RepairAccess(strConnstring);
if (strResult.Substring(0, 5) != "Error")
{
MessageBox.Show("数据库修复完成,请关闭系统,并重新登录!", "消息", MessageBoxButtons.OK, MessageBoxIcon.Warning);
this.Close();
}
else
{
MessageBox.Show("数据库修复失败::" + strResult, "消息", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
else
{
MessageBox.Show("数据库损坏,稍后将由上海办公室修复,请稍后重新登录!::" + ex.Message, "消息", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
}
else
{
if (File.Exists(strTempDB))
{
string strRepairUser = File.ReadAllText(strlock);
MessageBox.Show("登陆失败::数据库正在计算机: " + strRepairUser + " 启用自动修复中,请稍后重新登录!", "消息", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
else
{
MessageBox.Show("登陆失败::远端缓存库联络失败,请联系管理员!", "消息", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
}