// Heavily relies on the BaseObject table: Lists the rows of the selected table in the navbar
public void SetupTable(bool keepHighlightedButton)
{
AddNew.Background = Color(StandardNewColor);
Title.Text = SQLDB.CurrentTable;
CurrentlySelected = null;
TableSetup(RowsTable);
int count = 0;
using (var conn = AccessDB.Connect())
{
conn.Open();
string query = "SELECT * FROM BaseObject JOIN " + SQLDB.CurrentTable + " WHERE BaseObject_ID = BaseObjectID ORDER BY Name ASC";
using (SQLiteDataReader reader = SQLDB.Read(conn, query))
{
if (keepHighlightedButton)
{
while (reader.Read())
{
CreateRowWithHighlighted(reader, RowsTable, count++);
}
}
else
{
while (reader.Read())
{
CreateRow(reader, RowsTable, count++);
}
}
}
Count.Text = "Total: " + count;
conn.Close();
}
}
public ActionResult Register()
{
Session["error"] = null;
string conStr = ConfigurationManager.ConnectionStrings["ConStrs"].ConnectionString;
AccessDB accessDB = new AccessDB();
int id = 0;
using (SqlConnection con = new SqlConnection(conStr))
{
con.Open();
Person person = new Person
{
FName = Request["fname"],
LName = Request["lname"],
username = Request["username"],
password = Request["password"],
email = Request["email"]
};
id = new AccessDB().InsertPerson(person, con);
Session["username"] = person.username;
Session["password"] = person.password;
}
return(RedirectToAction("Index", "HomeAsync", null));
//accessDB.InsertPerson
}
public ActionResult Login()
{
Session["error"] = null;
string username = Request["username"];
string password = Request["password"];
List <Person> people = null;
string conStr = ConfigurationManager.ConnectionStrings["ConStrs"].ConnectionString;
using (SqlConnection con = new SqlConnection(conStr))
{
con.Open();
people = new AccessDB().Login_Check(username, password, con);;
if (people.Any())
{
Session["username"] = username;
Session["password"] = password;
return(RedirectToAction("Index", "HomeAsync", null));
}
else
{
Session["error"] = "Invalid login";
return(RedirectToAction("RegisterForm", "ProfileAsync", null));
}
}
}
/// <summary>
/// 编辑报价单按钮
/// </summary>
/// <param name="offersheet_code"></param>
private void editOfferSheet(string offersheet_code)
{
string sql = "select * from OSM_OFFER_SHEET ";
sql += "where OFFERSHEET_CODE = '" + offersheet_code + "'";
AccessDB adb = new AccessDB();
DataTable dt = adb.SQLTableQuery(sql);
Hashtable hashtable = new Hashtable();
if (dt.Rows.Count == 1)
{
DataRow dr = dt.Rows[0];
hashtable.Add("ID", dr["ID"]);
hashtable.Add("OFFERSHEET_CODE", dr["OFFERSHEET_CODE"]);
hashtable.Add("GMF_ID", dr["GMF_ID"]);
hashtable.Add("BJF_ID", dr["BJF_ID"]);
hashtable.Add("OFFERSHEET_TYPE", dr["OFFERSHEET_TYPE"]);
hashtable.Add("OFFERSHEET_DATE", dr["OFFERSHEET_DATE"]);
hashtable.Add("OFFERSHEET_STATE", dr["OFFERSHEET_STATE"]);
hashtable.Add("OFFERSHEET_REGION", dr["OFFERSHEET_REGION"]);
hashtable.Add("CONFIRM_DATE", dr["CONFIRM_DATE"]);
}
FormOSM_Offers_Add form_add_offers = new FormOSM_Offers_Add();
form_add_offers.dataGridView_editBtn_click_reaction(hashtable);
form_add_offers.setMainForm(main_form);
form_add_offers.setViewState(2);
form_add_offers.StartPosition = FormStartPosition.CenterParent;
form_add_offers.ShowDialog();
}
protected void Update_to_DB_Click(object sender, EventArgs e)
{
var db = new AccessDB();
db.QueryType = CommandType.StoredProcedure;
db.QueryString = "UpdateDataToAllTable";
var intern = new InternData();
intern.FirstName = first_name.Text;
intern.LastName = last_name.Text;
intern.FullName = first_name.Text + " " + last_name.Text;
intern.ContactNumber = contact_number.Text;
intern.Email = email_address.Text;
intern.Role = Int32.Parse(Role.SelectedValue.ToString());
intern.IsPrimary = PrimaryCheckBox.Checked;
intern.IsBusiness = IsBusinesCheckBox.Checked;
intern.DateOfBirth = DOB.SelectedDate.ToShortDateString();
intern.personID = interID;
db.Update(intern);
string message = intern.FullName + " You have Sucessfully Updated Your Details";
HttpContext.Current.Response.Redirect("SuccessfulPage.aspx?" + message);
}
protected void SubmitBtn_Click(object sender, EventArgs e)
{
AccessDB dbObj = new AccessDB();
dbObj.Open();
dbObj.Query = string.Format("Select emailaddress,CAST( AES_DECRYPT( passwd, 'kalli' ) AS CHAR( 100 ) ) from tbluserlogin where emailaddress ='{1}'", Constants.AESKey, UserNameTbx.Text);
dbObj.ExecuteQuery();
if (dbObj.Dataset.Tables[0].Rows.Count > 0)
{
Mail mailObj = new Mail();
mailObj.To = UserNameTbx.Text;
mailObj.Subject = "Reply: Forgot Password Request";
mailObj.MailBody = string.Format("Dear Ma'am/Sir, \r\n Username:{0} \r\n Password:{1} \r\n Regards, \r\n Admin team.", UserNameTbx.Text, dbObj.Dataset.Tables[0].Rows[0][1]);
mailObj.SendMailMessage();
VerificationLbl.Text = "Your password has been sent to your email.";
VerificationLbl.Visible = true;
}
else
{
VerificationLbl.Text = "The UserName provided is not valid.Kindly verify and retry / contact the Admin Team.";
VerificationLbl.Visible = true;
}
dbObj.Close();
}
public static bool DeleteMovie(Movie objMovie)
{
//Pre-step: Replace the general object parameter with the appropriate business class object that you are using to insert data in the underline database table
string SQLStatement = String.Empty;
//Uncomment either Example #1 or #2 to use appropriate connection string
//Example #1 for connecting to a remote SQL Server instance via IP address and SQL Server authenication..For Meramec
//string connectionString = "Server=mc-sluggo.stlcc.edu;Database=IS253_251;User Id=csharp2;Password=csharp2;";
//Example #2 for connecting to SQL Server locally with Windows Authenication. Change accordingly to your environment.
//string connectionString = @"Data Source=STEVIE-LAPTOP\MSSQLSERVER1;Integrated Security=True;Connect Timeout=15;Encrypt=False;TrustServerCertificate=False";
int rowsAffected = 0;
SqlCommand objCommand = null;
SqlConnection objConn = null;
string sqlString;
try
{
using (objConn = AccessDB.GetConnection())
{
//Open the connection to the datbase
objConn.Open();
sqlString = "DELETE Movie WHERE movie_number = @movie_number";
//Create a command object with the SQL statement
using (objCommand = new SqlCommand(sqlString, objConn))
{
//Use the command parameters method to set the paramater values of the SQL Insert statement
objCommand.Parameters.AddWithValue("@movie_number", objMovie.movie_number);
//Execute the SQL and return the number of rows affected
rowsAffected = objCommand.ExecuteNonQuery();
}
if (rowsAffected > 0)
{
return(true);
}
else
{
return(false);
}
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
//Finally will always be called in a try..catch..statem. You can use to to close the connection
//especially if an error is thrown
if (objConn != null)
{
objConn.Close();
}
}
}
public Automacao(string Caminho_, string sPesquisa_, string Controle_, AccessDB DB_)
{
DB = DB_;
Caminho = Caminho_;
sPesquisa = sPesquisa_;
Controle = Controle_;
dDataPesq = DateTime.Now.ToString("dd/mm/yyy hh:mm:ss");
}
public MainAction(AccessDB d,int y,int s,string u = "stu")
{
db = d;
year = y;
season = s;
useName = u;
useNum = db.GetUserNumber(useName);
}
/// <summary>
/// 检查是否可以发货，不能发货则生成对应的采购单
/// </summary>
/// <param name="hashList">货物列表</param>
/// <param name="order_id">订单ID</param>
/// <returns></returns>
private bool checkDelivery(List <Hashtable> hashList, string order_id)
{
List <Hashtable> purchaseList = new List <Hashtable>();
AccessDB adb = new AccessDB();
for (int i = 0; i < hashList.Count; i++)
{
Hashtable ht = hashList[i];
string product_id = ht["PRODUCT_ID"].ToString();
int hw_need_number = int.Parse(ht["HW_NUMBER"].ToString());
string sql = "select ID,HW_NUMBER from OSM_STORAGE where ID = " + product_id + "";
DataTable dt = adb.SQLTableQuery(sql);
if (dt.Rows.Count == 1)
{
DataRow dr = dt.Rows[0];
int hw_storage_number = int.Parse(dr["HW_NUMBER"].ToString());
int hw_diff_number = hw_need_number - hw_storage_number;
if (hw_diff_number > 0)
{
string updateSQL = "update OSM_STORAGE set HW_NUMBER = 0 where ID = " + product_id;
adb.SQLExecute(updateSQL);
ht["HW_NUMBER"] = hw_diff_number;
purchaseList.Add(ht);
}
else
{
string updateSQL = "update OSM_STORAGE set HW_NUMBER = " + (-hw_diff_number) + " where ID = " + product_id;
adb.SQLExecute(updateSQL);
break;
}
}
else
{
ht["HW_NUMBER"] = hw_need_number;
purchaseList.Add(ht);
}
}
if (purchaseList.Count == 0)
{
//string insertSQL = "insert into OSM_DILIVERY_SHEET(ORDER_ID,DILIVERY_STATE) values(" + order_id + ",'1')";
//if (adb.SQLExecute(insertSQL))
//{
// return true;
//}
return(true);
}
else
{
int count = createPurchaseSheet(purchaseList, order_id);
MessageBox.Show("库存不足，新增" + count + "条采购记录", "消息");
return(false);
}
}
public static List <Movie> GetMovie()
{
List <Movie> movieList = new List <Movie>(); //Establishes a list to load data into
string SQLStatement =
"SELECT movie_number, movie_title, Description, movie_year_made, genre_id, movie_rating, media_type, movie_retail_cost, copies_on_hand, image, trailer " +
"FROM Movie";
SqlCommand Command = null;
SqlConnection Conn = null;
SqlDataReader Reader = null;
try
{
using (Conn = AccessDB.GetConnection())
{
//Open the connection to the database
Conn.Open();
//Create a command object with the SQL statement
using (Command = new SqlCommand(SQLStatement, Conn))
{
//Execute the SQL and return a DataReader Object
using (Reader = Command.ExecuteReader())
{
while (Reader.Read())
{
Movie newMovie = new Movie();
newMovie.movie_number = Convert.ToInt32(Reader["movie_number"]);
newMovie.movie_title = Reader["movie_title"].ToString(); //Need to expand this to fill entire movie out
newMovie.Description = Reader["Description"].ToString();
newMovie.movie_year_made = Convert.ToInt32(Reader["movie_year_made"]);
newMovie.genre_id = Convert.ToInt32(Reader["genre_id"]);
newMovie.movie_rating = Reader["movie_rating"].ToString();
newMovie.media_type = Reader["media_type"].ToString();
newMovie.movie_retail_cost = Convert.ToInt32(Reader["movie_retail_cost"]);
newMovie.copies_on_hand = Convert.ToInt32(Reader["copies_on_hand"]);
newMovie.image = Reader["image"].ToString();
newMovie.trailer = Reader["trailer"].ToString();
//Add the Movie to the collection
movieList.Add(newMovie);
}
}
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (Conn != null)
{
Conn.Close();
}
}
return(movieList);
}
protected void RegisterButton_Click(object sender, EventArgs e)
{
AccessDB dbObj = new AccessDB();
dbObj.Open();
//verify if registration record already exists
dbObj.Query = string.Format("select userregisterationid from tbluserregisteration where EmailID='{0}'", Email.Text); ;
dbObj.ExecuteQuery();
if (dbObj.Dataset.Tables[0].Rows.Count > 0)
{
ConfirmationLabel.Style.Add("color", "Red");
ConfirmationLabel.Text = string.Format("Sorry! Our system shows an account is already registered with this email -{0}. If you have forgotten your password, use the 'Forgot Password' link to get your password.", Email.Text);
ConfirmationLabel.Visible = true;
}
else
{
dbObj.Dataset.Reset();
//Insert details into the db
dbObj.Query = string.Format(@"insert into tblUserRegisteration (FirstName,LastName,FamilyBranch,BornInto,HomePhone,EmailID,Passwd,Address1,Address2,City,State,Country,Pincode,RegistrationDate)
values('{0}','{1}','{2}','{3}','{4}','{5}',AES_ENCRYPT('{6}','{7}'),'{8}','{9}','{10}','{11}','{12}',{13},current_timestamp()); "
, FirstName.Text, LastName.Text, FamilyBranch.SelectedValue, rdlConnection.SelectedValue, PhoneNumber.Text, Email.Text, Password.Text, Constants.AESKey, Address1.Text, Address2.Text, CityDitrict.Text, State.Text
, Country.Text, int.Parse(PinCode.Text));
dbObj.ExecuteNonQuery();
//Get the current users' userregisterationid
dbObj.Dataset.Reset();
dbObj.Query = string.Format("select userregisterationid, registrationDate from tbluserregisteration where EmailID='{0}' and FirstName='{1}' and LastName='{2}'", Email.Text, FirstName.Text, LastName.Text); ;
dbObj.ExecuteQuery();
long UserRegID = (long)dbObj.Dataset.Tables[0].Rows[0][0];
DateTime dtReg = (DateTime)dbObj.Dataset.Tables[0].Rows[0][1];
//Mail to admin
string mailBody = string.Format(Constants.AdminMailText) + Environment.NewLine + string.Format("\n\nFirstName:{0}\nLastName:{1}\nFamily:{2}\nAddress:{3}\nEmail:{4}\nHomePhone:{5}\nRegisteration Date:{6}"
, FirstName.Text, LastName.Text, FamilyBranch.Text, string.Concat(Address1.Text, ",", Address2.Text, ",", CityDitrict.Text, "-", PinCode.Text, ",", State.Text, ",", Country.Text, ","), Email.Text, PhoneNumber.Text, dtReg)
+ Environment.NewLine + "http://www.Kallivayalil.com/ActivateUser.aspx?UserRegID=" + UserRegID.ToString();
dbObj.Dataset.Reset();
dbObj.Query = string.Format(@"Select emailaddress from tbluserlogin where isadmin=true");
dbObj.ExecuteQuery();
for (int i = 0; i < dbObj.Dataset.Tables[0].Rows.Count; i++)
{
SendMailMessage(dbObj.Dataset.Tables[0].Rows[i][0].ToString(), "*****@*****.**", mailBody);
}
//mail to user.
mailBody = string.Empty;
mailBody = string.Format(Constants.UserMailText) + Environment.NewLine + string.Format("\n\nFirstName:{0}\nLastName:{1}\nFamily:{2}\nAddress:{3}\nEmail:{4}\nHomePhone:{5}\nRegistration Date:{6}"
, FirstName.Text, LastName.Text, FamilyBranch.Text, string.Concat(Address1.Text, ",", Address2.Text, ",", CityDitrict.Text, "-", PinCode.Text, ",", State.Text, ",", Country.Text, ","), Email.Text, PhoneNumber.Text, dtReg);
SendMailMessage(Email.Text, "RegistrationMail @ Kallivayalil.com", mailBody);
ConfirmationLabel.Style.Add("color", "Green");
ConfirmationLabel.Text = "Thank You for registering. A confirmation email has been sent to the emailaddress you provided. We will review your information and your account will be activated at the earliest.";
ConfirmationLabel.Visible = true;
UserRegistration.Visible = false;
}
dbObj.Close();
}
private void BindData()
{
AccessDB dbObj = new AccessDB();
dbObj.Open();
dbObj.Query = string.Format("Select * from tblspecialevents where eventname ={0} and eventdate='{1}'", Session["EventName"], Session["EventDate"]);
dbObj.ExecuteQuery();
DetailsView1.DataSource = dbObj.Dataset;
DetailsView1.DataBind();
dbObj.Close();
}
/// <summary>
/// Procedure used to receive messages from DB.
/// </summary>
/// <param name="appName"> Application name from which messages will be returned. </param>
/// <param name="receiveTimeout"> Timeout used during execution of queries in seconds. Default: 30s. (schould be less tchan typical app pool recucle time = 90s )</param>
/// <returns></returns>
public List <NotificationMessage> ReceiveSubscription(string appName, int receiveTimeout = 30)
{
string schemaName = "[" + appName + "]";
SqlCommand command = new SqlCommand(schemaName + "." + ProcedureNameReceiveNotification);
command.Parameters.Add(AccessDB.CreateSqlParameter("V_ReceiveTimeout", SqlDbType.Int, receiveTimeout * 1000));
List <NotificationMessage> result = AccessDBInstance.SQLRunQueryProcedure <NotificationMessage>(command, null, 0);
return(result);
}
public void TestTable()
{
string mdbFilename = Path.Combine(Globals.TestDataPath, "Water Quality Testing.mdb");
AccessDB a = new AccessDB(mdbFilename);
DataTable tbl = a.Table("Water");
Assert.AreEqual(5880, tbl.Rows.Count);
// Console.WriteLine(tbl.Rows.Count);
}
public List <AORCachedGroup> GetAORGroupsForArea(int areaId)
{
using (var access = new AccessDB())
{
var query = access.Groups.Where(g => g.Areas.Any(a => a.AreaId == areaId));
var result = query.ToList();
return(result);
}
}
public List <Permission> GetPermissionsForDNAs(long areaId) //vrati se ovde jer vadim perms za DNA, iako pise par. areaID
{
using (var access = new AccessDB())
{
var query = access.Permissions.Where(d => d.DNAs.Any(a => a.DNAId == areaId));
var result = query.ToList();
return(result);
}
}
public List <AORCachedArea> GetModelAORAreas() //access.Areas.Where(a => a.Groups.Any(d => d.GroupId == 4)); // ovo radi okej
{
using (var access = new AccessDB())
{
var query = access.Areas;
var result = query.ToList();
return(result);
}
}
/// <summary>
/// 获取零部件的条形码
/// </summary>
/// <param name="partCodes">要获取条形码的零部件</param>
/// <param name="barcodes">零部件代码与条形码配对的字典, 如果某零部件没有获取到条形码则该零部件value值为null</param>
/// <param name="err">错误信息, 如果没有则输出值为null</param>
/// <returns>成功返回true, 失败返回false</returns>
public bool GetBarcodeOfParts(string[] partCodes, out Dictionary <string, string> barcodes, out string err)
{
Debug.Assert(partCodes != null && partCodes.Length > 0, "参数 'partCodes' 不能为 null");
barcodes = null;
err = null;
if (partCodes == null)
{
err = "partCodes 不能为 null";
return(false);
}
string strPartCodes = "";
for (int i = 0; i < partCodes.Length; i++)
{
strPartCodes = strPartCodes.Insert(strPartCodes.Length, partCodes[i]);
if (i != partCodes.Length - 1)
{
strPartCodes = strPartCodes.Insert(strPartCodes.Length, ",");
}
}
// 执行存储过程从数据库中获取数据表
Hashtable paramTable = new Hashtable();
paramTable.Add("@PartCodes", strPartCodes);
DataSet ds = new DataSet();
if (!AccessDB.ExecuteDbProcedure("SelectPartBarcode", paramTable, ds, out err))
{
return(false);
}
DataTable dt = ds.Tables[0];
if (dt == null || dt.Rows.Count == 0)
{
err = "没有获取到装配条码信息";
return(false);
}
barcodes = new Dictionary <string, string>(dt.Rows.Count);
for (int i = 0; i < dt.Rows.Count; i++)
{
barcodes.Add(dt.Rows[i][0].ToString(), dt.Rows[i][1].ToString());
}
return(true);
}
public static bool UpdateGenre(Genre objGenre)
{
string SQLStatement = String.Empty;
int rowsAffected = 0;
SqlCommand objCommand = null;
SqlConnection objConn = null;
string sqlString;
try
{
using (objConn = AccessDB.GetConnection())
{
//Open the connection to the datbase
objConn.Open();
sqlString = "UPDATE Genre " + Environment.NewLine +
"set name = @genre_name " + Environment.NewLine +
"where id = @genre_id ";
//Create a command object with the SQL statement
using (objCommand = new SqlCommand(sqlString, objConn))
{
//Use the command parameters method to set the paramater values of the SQL Insert statement
objCommand.Parameters.AddWithValue("@genre_name", objGenre.Name);
objCommand.Parameters.AddWithValue("@genre_id", objGenre.ID);
//Execute the SQL and return the number of rows affected
rowsAffected = objCommand.ExecuteNonQuery();
}
if (rowsAffected > 0)
{
return(true);
}
else
{
return(false);
}
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
//Finally will always be called in a try..catch..statem. You can use to to close the connection
//especially if an error is thrown
if (objConn != null)
{
objConn.Close();
}
}
}
public static Genre GetGenre(int genreID)
{
//Pre-step: Replace the general object parameter with the appropriate data type parameter for retrieving a specific item from the specific database table.
string SQLStatement = String.Empty;
//Change the MyCustomObject references to your customer business object
//Genre objTemp = new Genre();
string sqlString = "Select id, name from genre where id = @genre_id ";
SqlCommand Command = null;
SqlConnection Conn = null;
SqlDataReader Reader = null;
Genre newGenre = null;
try
{
using (Conn = AccessDB.GetConnection())
{
//Open the connection to the datbase
Conn.Open();
//Create a command object with the SQL statement
using (Command = new SqlCommand(sqlString, Conn))
{
//Set command parameter
Command.Parameters.AddWithValue("@genre_id", genreID);
//Execute the SQL and return a DataReader
using (Reader = Command.ExecuteReader())
{
while (Reader.Read())
{
newGenre = new Genre();
//Fill the customer object if found
newGenre.ID = Convert.ToInt32(Reader["ID"]);
newGenre.Name = Reader["Name"].ToString();
}
}
}
return(newGenre);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
//Finally will always be called in a try..catch..statem. You can use to to close the connection
//especially if an error is thrown
if (Conn != null)
{
Conn.Close();
}
}
}
public List <AORCachedGroup> GetModelAORGroup()
{
using (var access = new AccessDB())
{
var query = (from a in access.Groups // ne radi Include do SMs
select a);
var result = query.ToList();
return(result);
}
}
private void CreateSeriesAF(AccessDB mdb, string plantName, string dataType, int parentID)
{
string units = "acre-feet";
string name = plantName + " " + dataType + " " + units;
string sheetName = plantName + " " + dataType;
string cs = "FileName=" + m_mdbFileName + ";PlantName=" + plantName + ";DataType=" + dataType; // connection string
cs = ConnectionStringUtility.MakeFileNameRelative(cs, m_databasePath);
seriesCatalog.AddSeriesCatalogRow(ID++, parentID, false, ID, "BpaHydsimSeriesAccess", name, sheetName, units,
TimeInterval.Monthly.ToString(), "Parameter", "", "BpaHydsimSeriesAccess", cs, "", "", true);
}
public List <User> GetAllUsers()
{
using (var access = new AccessDB())
{
var query = (from a in access.Users // vrati se za vezu area -> user
select a);
var c = query.ToList();
return(c);
}
}
/// <summary>
/// 获取由材料类别信息产生的存储过程参数表
/// </summary>
/// <param name="materialTypeInfo">材料类别信息</param>
/// <returns>产生的参数表</returns>
private Hashtable GetParamTable(MaterialTypeData materialTypeInfo)
{
Dictionary <string, object> dicParam = new Dictionary <string, object>();
dicParam.Add("@MaterialTypeCode", materialTypeInfo.MaterialTypeCode);
dicParam.Add("@MaterialTypeName", materialTypeInfo.MaterialTypeName);
dicParam.Add("@MaterialTypeGrade", materialTypeInfo.MaterialTypeGrade);
dicParam.Add("@IsEnd", materialTypeInfo.IsEnd);
return(AccessDB.SetSPParam(dicParam));
}
/// <summary>
/// 根据ID删除货物明细记录
/// </summary>
/// <param name="hw_id"></param>
private void DelHwByID(string hw_id)
{
AccessDB adb = new AccessDB();
string whereString = "where ID = " + hw_id;
if (adb.SQLTableDelete("OSM_HW", whereString) > 0)
{
MessageBox.Show("删除成功！", "消息");
queryByHW(dataGridView_HW);
}
}
/// <summary>
/// 刷新货物列表
/// </summary>
public void refresh_hw_datagrid()
{
string sql = "select * from OSM_HW_LIST_V where OFFERSHEET_CODE = '" + textBox_OFFERSHEET_CODE.Text + "' ";
sql += "order by ID DESC";
AccessDB adb = new AccessDB();
DataTable dt = adb.SQLTableQuery(sql);
dataGridView_HW.DataSource = dt;
}
/// <summary>
/// 对应报价单审核不通过
/// </summary>
/// <param name="offerSheetID">报价单ID</param>
private void FailOfferSheet(string offerSheetID)
{
string sql = "update OSM_OFFER_SHEET set OFFERSHEET_STATE = '3' where ID = " + offerSheetID;
AccessDB adb = new AccessDB();
if (adb.SQLExecute(sql))
{
main_form.TSMItem_offer_aduit_Refresh();
}
}
/// <summary>
/// 到货确认按钮点击事件
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button_OK_Click(object sender, EventArgs e)
{
if (!string.IsNullOrWhiteSpace(purchase_id) && !string.IsNullOrWhiteSpace(product_id) && !string.IsNullOrWhiteSpace(order_id))
{
if (string.IsNullOrWhiteSpace(textBox_HWNumber.Text))
{
MessageBox.Show("请填写到货数量,谢谢！", "警告");
return;
}
int number_want = int.Parse(textBox_PurchaseNumber.Text);
int number_real = int.Parse(textBox_HWNumber.Text);
if (number_want > number_real)
{
//需采购数量大于到货数量，不能确认
MessageBox.Show("到货数量小于需求数量，无法确认到货！", "警告");
return;
}
else
{
//到货数量大于等于需采购数量,改变采购记录状态，改变库存数量
AccessDB adb = new AccessDB();
string sql = "select HW_NUMBER,REAL_NUMBER from OSM_STORAGE where ID = " + product_id;
DataTable dt = adb.SQLTableQuery(sql);
DataRow dr = dt.Rows[0];
int hw_number = int.Parse(dr["HW_NUMBER"].ToString());
int real_number = int.Parse(dr["REAL_NUMBER"].ToString());
real_number += number_real;
hw_number += number_real - number_want;
string updateSQL = "update OSM_STORAGE set HW_NUMBER = " + hw_number + ", REAL_NUMBER = " + real_number + " where ID = " + product_id;
if (adb.SQLExecute(updateSQL))
{
updateSQL = "update OSM_PURCHASE_SHEET set PURCHASE_STATE = '2' where ID = " + purchase_id;
if (adb.SQLExecute(updateSQL))
{
MessageBox.Show("到货确认成功！", "消息");
fWindow.refresh_dataGridView();
checkOrder(order_id);
this.Close();
this.Dispose();
}
}
}
}
else
{
MessageBox.Show("UNEXCEPTED ERROR!", "ERROR");
}
}
/// <summary>
/// 重置允许加班调休的时间
/// </summary>
/// <param name="error">错误信息</param>
/// <returns>成功返回True，失败返回False</returns>
public bool AddAttendanceSummaryByAllowOverTime(out string error)
{
error = "";
if (!AccessDB.ExecuteDbProcedure("HR_Update_OverTime", out error))
{
return(false);
}
return(true);
}
/// <summary>
/// 尾页按钮点击事件
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button_last_page_Click(object sender, EventArgs e)
{
pageIndex = pageCount;
AccessDB adb = new AccessDB();
DataTable dt = adb.ExecutePager(pageIndex, pageSize, keyString, showString, queryString, whereString, orderString, out pageCount, out recordSum);
dgv.DataSource = dt;
textBox_page_index.Text = pageIndex.ToString();
label_current_page.Text = pageIndex.ToString() + "/" + pageCount.ToString();
label_record_sum.Text = recordSum.ToString();
}
public static void CreatePiscesTree(string fileName, PiscesFolder root,
TimeSeriesDatabase db)
{
mi = new Model();
s_db = db;
sdi = db.NextSDI();
studyFolderID = sdi;
int parentID = root.ID;
seriesCatalog = new TimeSeriesDatabaseDataSet.SeriesCatalogDataTable();
if (File.Exists(fileName))
{
XYFileReader.Read(mi, fileName);
m_xyFilename = Path.GetFileNameWithoutExtension(fileName);
}
else
{
throw new FileNotFoundException("Modsim xy file is not found " + fileName);
}
string mdbJetName = Path.Combine(Path.GetDirectoryName(fileName), m_xyFilename + "OUTPUT.mdb");
string mdbAceName = Path.Combine(Path.GetDirectoryName(fileName), m_xyFilename + "OUTPUT.accdb");
if (File.Exists(mdbAceName))
{
m_databaseName = mdbAceName;
}
else
{
m_databaseName = mdbJetName;
}
if (File.Exists(m_databaseName))
{
m_db = new AccessDB(m_databaseName);
dir = Path.GetDirectoryName(Path.GetFullPath(m_databaseName));
//AddNewRow(sdi,parentID,true, "", mi.name, "");
AddNewRow(sdi, parentID, true, "", Path.GetFileNameWithoutExtension(fileName), "");
ReservoirsTree();
DemandsTree();
RiverLinksTree();
TotalsTree();
}
else
{
throw new FileNotFoundException(" MODSIM output not found " + m_databaseName);
}
//DataTableOutput.Write(seriesCatalog, @"C:\temp\a.csv",false);
db.Server.SaveTable(seriesCatalog);
db.RefreshFolder(root);
}
public static bool AddGenre(Genre objGenre)
{
//Pre-step: Replace the general object parameter with the appropriate business class object that you are using to insert data in the underline database table
string SQLStatement = String.Empty;
int rowsAffected = 0;
SqlCommand objCommand = null;
SqlConnection objConn = null;
string sqlString;
try
{
using (objConn = AccessDB.GetConnection())
{
//Open the connection to the datbase
objConn.Open();
sqlString = "INSERT into Genre values (@genre_id, @genre_name)";
//Create a command object with the SQL statement
using (objCommand = new SqlCommand(sqlString, objConn))
{
//Use the command parameters method to set the paramater values of the SQL Insert statement
objCommand.Parameters.AddWithValue("@genre_id", objGenre.ID);
objCommand.Parameters.AddWithValue("@genre_name", objGenre.Name);
//Execute the SQL and return the number of rows affected
rowsAffected = objCommand.ExecuteNonQuery();
}
if (rowsAffected > 0)
{
return(true);
}
else
{
return(false);
}
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
//Finally will always be called in a try..catch..statem. You can use to to close the connection
//especially if an error is thrown
if (objConn != null)
{
objConn.Close();
}
}
}
/// <summary>
/// 初始化所以表
/// </summary>
private void InitDataTable()
{
OrginHeteMineralDt = new DataTable();
OrginHomoMineralDt = new DataTable();
OrginHeteMineralDt = AccessDB.Query(new HeterogeneousMineralInfo());
OrginHomoMineralDt = AccessDB.Query(new HomogeneousMineralInfo());
OrginHeteMineralDt.TableName = "非均质矿物";
OrginHomoMineralDt.TableName = "均质矿物";
OrginMineralDs = new DataSet();
OrginMineralDs.Tables.Add(OrginHeteMineralDt.Copy());
OrginMineralDs.Tables.Add(OrginHomoMineralDt.Copy());
}
public static void CreatePiscesTree(string fileName, PiscesFolder root,
TimeSeriesDatabase db)
{
mi = new Model();
s_db = db;
sdi = db.NextSDI();
studyFolderID = sdi;
int parentID = root.ID;
seriesCatalog = new TimeSeriesDatabaseDataSet.SeriesCatalogDataTable();
if (File.Exists(fileName))
{
XYFileReader.Read(mi, fileName);
m_xyFilename = Path.GetFileNameWithoutExtension(fileName);
}
else
{
throw new FileNotFoundException("Modsim xy file is not found " + fileName);
}
string mdbJetName = Path.Combine(Path.GetDirectoryName(fileName), m_xyFilename + "OUTPUT.mdb");
string mdbAceName = Path.Combine(Path.GetDirectoryName(fileName), m_xyFilename + "OUTPUT.accdb");
if (File.Exists(mdbAceName))
{
m_databaseName = mdbAceName;
}
else
m_databaseName = mdbJetName;
if (File.Exists(m_databaseName))
{
m_db = new AccessDB(m_databaseName);
dir = Path.GetDirectoryName(Path.GetFullPath(m_databaseName));
//AddNewRow(sdi,parentID,true, "", mi.name, "");
AddNewRow(sdi, parentID, true, "", Path.GetFileNameWithoutExtension(fileName), "");
ReservoirsTree();
DemandsTree();
RiverLinksTree();
TotalsTree();
}
else
{
throw new FileNotFoundException(" MODSIM output not found " + m_databaseName);
}
//DataTableOutput.Write(seriesCatalog, @"C:\temp\a.csv",false);
db.Server.SaveTable(seriesCatalog);
db.RefreshFolder(root);
}
public DataSet GetData()
{
string eventQuery = string.Empty;
if (Session["UserLogin"] != null)
{
eventQuery = "Select eventname, eventdate, eventtype, eventdetails from tblspecialevents ";
}
else
{
eventQuery = "Select eventname, eventdate, eventtype, eventdetails from tblspecialevents where IsPublic=1";
}
dbObj = new AccessDB();
dbObj.Open();
dbObj.Query = eventQuery;
dbObj.ExecuteQuery();
DataSet ds = new DataSet();
DataTable dt = new DataTable("News");
DataRow dr;
dt.Columns.Add(new DataColumn("Id", typeof(Int32)));
dt.Columns.Add(new DataColumn("Url", typeof(string)));
dt.Columns.Add(new DataColumn("Desc", typeof(string)));
string eventImage="images/celebration.gif";
string eventTitle = string.Empty;
string eventDetails = string.Empty;
string eventType = string.Empty;
DateTime dtobj = DateTime.Now;
for (int i = 0; i < dbObj.Dataset.Tables[0].Rows.Count; i++)
{
dr = dt.NewRow();
dr[0] = i + 1;
dtobj = (DateTime)dbObj.Dataset.Tables[0].Rows[i][1];
eventTitle = String.Format("{0} on {1}/{2}/{3}<br/>", dbObj.Dataset.Tables[0].Rows[i][0], dtobj.Day, dtobj.Month, dtobj.Year);
eventDetails = String.Format("<u>Event Details</u>:<br/> {0}", dbObj.Dataset.Tables[0].Rows[i][3]);
eventType = dbObj.Dataset.Tables[0].Rows[i][2].ToString();
eventImage=GetEventImage(eventType);
dr[1] = string.Format("javascript:openQuickAddDialog(1000, 101, '{0}','{1}','{2}');", eventTitle, eventDetails, eventImage);
//dr[1] = string.Format("Event.aspx?EventName={0}&EventDate={3}-{2}-{1}", dbObj.Dataset.Tables[0].Rows[i][0], dtobj.Day, dtobj.Month, dtobj.Year);
dr[2] = eventTitle;
dt.Rows.Add(dr);
}
ds.Tables.Add(dt);
Session["dt"] = dt;
dbObj.Close();
return ds;
}
public SelectAccessSeries(string filename)
{
InitializeComponent();
db = new AccessDB(filename);
this.labelFileName.Text = filename;
comboBoxTableNames.Items.Clear();
comboBoxTableNames.Items.AddRange(db.TableNames());
comboBoxTableNames.Items.AddRange(db.QueryNames());
if (comboBoxTableNames.Items.Count > 0)
{
comboBoxTableNames.SelectedIndex = 0;
}
ReloadDropDowns();
}
protected void AddEvntBtn_Click(object sender, EventArgs e)
{
dbObj = new AccessDB();
dbObj.Open();
bool isPublic = false;
if (MemOnlyRb.Checked)
isPublic = true;
dbObj.Query = string.Format(@"insert into tblspecialevents(EventName,EventType,EventDetails,StartDate,EventDate,ContactPerson,ContactNumber,IsPublic,CreatedBy,updatedby,updateddate) values
('{0}','{1}','{2}','{3}','{4}','{5}','{6}',{7},'{8}','{8}',curdate())"
, EvntNameTbx.Text, EvntTypeDdl.SelectedValue, EvntDescTbx.Text, Convert.ToDateTime(StartDtTbx.Text).ToString("yyyy-MM-dd hh:mm:ss"), Convert.ToDateTime(EndDtTbx.Text).ToString("yyyy-MM-dd hh:mm:ss"), ContactPersonTbx.Text, ContactNumTbx.Text,
isPublic, Session["UserName"]);
dbObj.ExecuteNonQuery();
dbObj.Close();
divSuccessLabel.Visible = true;
divAddevent.Visible = false;
}
void CreateImage(int ID,bool isSpouse)
{
try
{
AccessDB dbObj = new AccessDB();
dbObj.Open();
if(isSpouse)
dbObj.Query = "Select Photo from tblSpouse where SpID=" + ID;
else
dbObj.Query = "Select Photo from tblUserProfile where userprofileID="+ ID;
byte[] _buf = (byte[])dbObj.ExecuteScalar();
//stream it back in the HTTP response
Response.BinaryWrite(_buf);
}
catch (Exception ex)
{ }
}
protected void btnCrop_Click(object sender, EventArgs e)
{
try
{
if (W.Value != string.Empty)
{
int w = Convert.ToInt32(W.Value);
int h = Convert.ToInt32(H.Value);
int x = Convert.ToInt32(X.Value);
int y = Convert.ToInt32(Y.Value);
AccessDB dbObj = new AccessDB();
dbObj.Open();
dbObj.Query = "Select Photo from tblSpouse where spID=?";
dbObj.command.Parameters.Add(new System.Data.Odbc.OdbcParameter("spID", Session["queryID"]));
byte[] _buf = (byte[])dbObj.ExecuteScalar();
dbObj.Close();
MemoryStream msOrig = new MemoryStream(_buf);
byte[] CropImage = Crop(msOrig, w, h, x, y);
using (MemoryStream ms = new MemoryStream(CropImage, 0, CropImage.Length))
{
ms.Write(CropImage, 0, CropImage.Length);
Byte[] Cropped = ms.ToArray();
AccessDB dbObj1 = new AccessDB();
dbObj1.Open();
dbObj1.Query = "Update tblSpouse SET Photo=? where SpID=?";
dbObj1.command.Parameters.Add(new System.Data.Odbc.OdbcParameter("Photo", Cropped));
dbObj.command.Parameters.Add(new System.Data.Odbc.OdbcParameter("SpID", Session["queryID"]));
dbObj1.command.Prepare();
dbObj1.ExecuteNonQuery();
}
}
}
catch (Exception ex)
{
}
finally
{
pnlCrop.Visible = false;
pnlCropped.Visible = true;
}
}
private void refreshGrid()
{
DataSet ds = new DataSet();
ds.Tables.Add(new DataTable());
DataTable dt = new DataTable();
dt.Columns.Add("EventName");
dt.Columns.Add("EventType");
dt.Columns.Add("EventDetails");
dt.Columns.Add("EventDate");
dt.Columns.Add("ContactPerson");
dt.Columns.Add("ContactNumber");
AccessDB dbobj = new AccessDB();
dbobj.Open();
//if (d3.Text == string.Empty)
// d3.Text = "01.01.2000";
//if (d4.Text == string.Empty)
// d4.Text = "12.31.2030";
string whereclause = string.Empty;
if (d3.Text != string.Empty && d4.Text != string.Empty)
whereclause = string.Format(" where eventdate between STR_TO_DATE('{0}','%M %d, %Y') and STR_TO_DATE('{1}','%M %d, %Y')", d3.Text, d4.Text);
dbobj.Dataset.Reset();
dbobj.Query = string.Format("Select SocialEventID,eventname as EventName,eventtype as EventType,eventdetails as EventDetails,startdate as StartDate,DATE_FORMAT(eventdate, '%a %d %b, %Y') as EventDate,contactperson as ContactPerson,contactnumber as ContactNumber,ispublic,updatedby,DATE_FORMAT(updateddate,'%d/%m/%y') as updateddate from tblspecialevents" + whereclause);
dbobj.ExecuteQuery();
for (int i = 0; i < dbobj.Dataset.Tables[0].Rows.Count; i++)
{
dt.ImportRow(dbobj.Dataset.Tables[0].Rows[i]);
}
GridView1.DataSource = dt;
GridView1.DataBind();
dbobj.Close();
}
public ExploitMarketAction(AccessDB d, int y, int s, string u = "stu")
: base(d,y,s,u)
{
}
public DepreciationAction(AccessDB d, int y, int s, string u = "stu")
: base(d,y,s,u)
{
}
public TransportMaterialAction(AccessDB d, int y, int s, string u = "stu")
: base(d,y,s,u)
{
}
protected void LoginButton_Click(object sender, ImageClickEventArgs e)
{
AccessDB dbObj = new AccessDB();
string userName = string.Empty;
string paswd = string.Empty;
//Retrieving the Username and Password entered by the user.
userName = Login1.UserName;
paswd = Login1.Password;
dbObj.Open();
dbObj.Query = string.Format("Select * from tbluserlogin where EmailAddress='{0}' and Passwd=AES_ENCRYPT('{1}','{2}')"
, userName, paswd,Constants.AESKey);
dbObj.ExecuteQuery();
if (dbObj.Dataset.Tables[0].Rows.Count > 0)
{
//Username and pasword exists for this user.
Session.Add("UserLogin", userName);
Session.Add("ID", dbObj.Dataset.Tables[0].Rows[0]["UserProfileID"]);
Session.Add("IsAdmin", dbObj.Dataset.Tables[0].Rows[0][7]);
Session.Add("PType", dbObj.Dataset.Tables[0].Rows[0]["ProfileType"]);
dbObj.Dataset.Clear();
dbObj.Close();
dbObj.Query = string.Format("Select * from tbluserprofile where UserProfileID={0}", Session["ID"]);
dbObj.ExecuteQuery();
if (dbObj.Dataset.Tables[0].Rows.Count > 0)
{
Session.Add("UserName", dbObj.Dataset.Tables[0].Rows[0]["FirstName"] + " " + dbObj.Dataset.Tables[0].Rows[0]["LastName"]);
}
if (Session["PageToLoad"] != null)
{
Response.Redirect(Session["PageToLoad"].ToString());
Session.Remove("PageToLoad");
}
else
Response.Redirect("Default.aspx");
}
else
{
dbObj.Query = string.Format("Select * from tbluserlogin where EmailAddress='{0}'", userName);
dbObj.ExecuteQuery();
if (dbObj.Dataset.Tables[0].Rows.Count > 0)
{
//Username exists but not activated.
dbObj.Dataset.Clear();
dbObj.Close();
Login1.FailureText = "Login Failed. Incorrect Login Information.";
Login1.FailureAction = LoginFailureAction.Refresh;
}
else
{
dbObj.Query = string.Format("Select * from tbluserregisteration where EmailId='{0}'", userName);
dbObj.ExecuteQuery();
if (dbObj.Dataset.Tables[0].Rows.Count > 0)
{
//Username exists but not activated.
dbObj.Dataset.Clear();
dbObj.Close();
Login1.FailureText = "Your account has not been activated by the Administrator. Sorry for the delay.";
Login1.FailureAction = LoginFailureAction.Refresh;
}
else
{
Login1.FailureText = "Invalid User Name and Password.";
Login1.FailureAction = LoginFailureAction.Refresh;
}
}
}
}
private void registerExistingUser(out string emailID, out string activatedUser)
{
dbObj = new AccessDB();
dbObj.Open();
bool isAdmin = false;
char ProfileType = 'U';
//Fetch the row for the registeration table.
dbObj.Query = string.Format(@"Select borninto,emailID,passwd,firstName,LastName,FamilyBranch,HomePhone
,Address1,Address2,city,state,country,pincode from tbluserregisteration
where userregisterationid={0} "
, Session["UserRegID"]);
dbObj.ExecuteQuery();
if (CheckBox1.Checked)
isAdmin = true;
if (dbObj.Dataset.Tables[0].Rows[0][0].ToString() == "M")
ProfileType = 'S';
//insert a row into the tbluserlogin table.Link the profile ID to the Login table.
string temp = string.Format(@"Insert into tbluserlogin (userprofileid,profiletype,emailaddress,passwd,
creationdate,updationdate,updatedby,activationdate,activatedby
,isadmin) values({0},'{6}','{1}',(select passwd as pass from tbluserregisteration where userregisterationid = {5}),curdate(),curdate(),'{2}',curdate(),'{3}',{4})",
Session["SelectedProfileID"], dbObj.Dataset.Tables[0].Rows[0][1]
, Session["UserLogin"], Session["UserLogin"], isAdmin, Session["UserRegID"],ProfileType);
emailID = (string)dbObj.Dataset.Tables[0].Rows[0][1];
dbObj.Query = temp;
dbObj.ExecuteNonQuery();
isAdmin = false;
if (RadioButtonList1.Items[0].Selected)
{
//update isactive,updation date,updated by in tbluserprofile.
dbObj.Query = string.Format(@"Update tbluserprofile set Isactive=true,createddate=curdate()
,updateddate=curdate(),updatedby='{0}',firstName='{2}',lastName='{3}',FamilyBranch='{4}',HomePhone='{5}'
,Address1='{6}',Address2='{7}',city='{8}',state='{9}',country='{10}',pincode='{11}' where UserProfileID = {1}"
, Session["UserLogin"], Session["SelectedProfileID"], dbObj.Dataset.Tables[0].Rows[0][4]
, dbObj.Dataset.Tables[0].Rows[0][5], dbObj.Dataset.Tables[0].Rows[0][6], dbObj.Dataset.Tables[0].Rows[0][7]
, dbObj.Dataset.Tables[0].Rows[0][8], dbObj.Dataset.Tables[0].Rows[0][9], dbObj.Dataset.Tables[0].Rows[0][10]
, dbObj.Dataset.Tables[0].Rows[0][11], dbObj.Dataset.Tables[0].Rows[0][12], dbObj.Dataset.Tables[0].Rows[0][13]);
dbObj.ExecuteNonQuery();
}
else if (RadioButtonList1.Items[1].Selected)
{
//update isactive,updation date,updated by in tbluserprofile.
dbObj.Query = string.Format(@"Update tbluserprofile set Isactive=true,createddate=curdate()
,updateddate=curdate(),updatedby='{0}' where UserProfileID = {1}"
, Session["UserLogin"], Session["SelectedProfileID"]);
dbObj.ExecuteNonQuery();
}
dbObj.Query = string.Format(@"select emailID from tbluserregisteration where userregisterationid={0} "
, Session["UserRegID"]);
dbObj.ExecuteQuery();
activatedUser = dbObj.Dataset.Tables[0].Rows[0][0].ToString();
//delete entry from the registeration table.
dbObj.Query = string.Format(@"delete from tbluserregisteration
where userregisterationid={0} "
, Session["UserRegID"]);
dbObj.ExecuteNonQuery();
}
public GatheringAction(AccessDB d, int y, int s, string u = "stu")
: base(d,y,s,u)
{
}
protected void btnUpload_Click(object sender, EventArgs e)
{
Boolean FileOK = false;
Boolean FileSaved = false;
if (FileUpload1.HasFile)
{
Session["WorkingImage"] = FileUpload1.FileName;
String FileExtension = Path.GetExtension(Session["WorkingImage"].ToString()).ToLower();
String[] allowedExtensions = { ".png", ".jpeg", ".jpg", ".gif" };
for (int i = 0; i < allowedExtensions.Length; i++)
{
if (FileExtension == allowedExtensions[i])
{
FileOK = true;
}
}
}
if (FileOK)
{
try
{
Byte[] file = FileUpload1.FileBytes;
AccessDB dbObj = new AccessDB();
dbObj.Open();
dbObj.Query = "Update tblUserProfile SET Photo=? where UserProfileID=?";
dbObj.command.Parameters.Add(new System.Data.Odbc.OdbcParameter("Photo", file));
dbObj.command.Parameters.Add(new System.Data.Odbc.OdbcParameter("UserProfileID", Session["ID"]));
dbObj.command.Prepare();
dbObj.ExecuteNonQuery();
dbObj.Close();
FileSaved = true;
}
catch (Exception ex)
{
lblError.Text = "File could not be uploaded." + ex.Message.ToString();
lblError.Visible = true;
FileSaved = false;
}
}
else
{
lblError.Text = "Cannot accept files of this type.";
lblError.Visible = true;
}
if (FileSaved)
{
pnlUpload.Visible = false;
pnlCrop.Visible = true;
pnlCropped.Visible = false;
}
}
public ShortTermLoanAction(AccessDB d, int y, int s, string u = "stu")
: base(d,y,s,u)
{
}
public PayExpenseAction(AccessDB d, int y, int s, string u = "stu")
: base(d,y,s,u)
{
}
public AppManager()
{
DataAccess = new AccessDB(dbINFO._SRV, dbINFO._CAT, dbINFO._UID, dbINFO._PWD);
AppLogin = new AppLoginHelper();
Utility = new UtilityHelper();
SendMail = new MailHelper();
Report = new ReportHelper(Utility, dbINFO._SRV, dbINFO._CAT, dbINFO._UID, dbINFO._PWD);
}
/*
* 每个季度初都要调用setNewSeasonCash函数确定新季度的现金初始值 *
* */
public NewSeasonCountingAction(AccessDB d, int y, int s, string u = "stu")
: base(d, y, s, u)
{
}
public QueryExecutor(AccessDB ADB)
{
__AccessDB = ADB;
}
public StartProcessAction(AccessDB d, int y, int s, string u = "stu")
: base(d,y,s,u)
{
}
public DeliveryProductAction(AccessDB d, int y, int s, string u = "stu")
: base(d,y,s,u)
{
}
/*
* 广告费投放
* */
public PutAdvertisementAction(AccessDB a, int y, int s, string u = "stu")
: base(a,y,s,u)
{
db = a;
}
protected void Page_Load(object sender, EventArgs e)
{
string UserName = (string)Session["UserLogin"];
if (UserName == null)
{
Session.Add("PageToLoad", "http://www.kallivayalil.com/Kallivayalil/LinkProfile.aspx");
Response.Redirect("default.aspx");
}
if (Session["SelectedProfileID"] == null)
{
ProfileDetailslbl.Visible = false;
RadioButtonList1.Visible = false;
NewUserLbl.ForeColor = Color.Green;
NewUserLbl.Visible = true;
}
else
{
if (Session["Borninto"].ToString() == "B")
{
dbObj = new AccessDB();
dbObj.Dataset.Reset();
dbObj.Query = string.Format(@"SELECT tbluserprofile.UserProfileID,Salutation, FirstName, MiddleName,
LastName, PreferredName, Gender, FamilyBranch, HouseName, BornOn, MaritalStatus,
Occupation, Employer, AlternateEmailAddress, Address1,Address2, City, State, Pincode,
Country, HomePhone, MobilePhone, Website FROM tbluserprofile WHERE
((UserProfileID = {0}) and IsActive!=1)", Session["SelectedProfileID"]);
dbObj.ExecuteQuery();
DetailsView2.DataSource = dbObj.Dataset.Tables[0];
DetailsView2.DataBind();
dbObj.Close();
}
else
{
dbObj = new AccessDB();
dbObj.Dataset.Reset();
dbObj.Query = string.Format(@"SELECT tblspouse.spid,tblspouse.Salutation, tblspouse.FirstName,
tblspouse.LastName, tblspouse.PreferredName, tblspouse.Gender, tblspouse.Familyname, tblspouse.BornOn,
tbluserprofile.City, tbluserprofile.State, tbluserprofile.Pincode,
tbluserprofile.Country, tbluserprofile.mobilePhone FROM tblspouse,tbluserprofile WHERE
((spid = {0}) and tblspouse.IsActive!=1)", Session["SelectedProfileID"]);
dbObj.ExecuteQuery();
DetailsView2.DataSource = dbObj.Dataset.Tables[0];
DetailsView2.DataBind();
dbObj.Close();
}
}
}
private void registerNewUser(out string emailID, out string activatedUser)
{
newUser = true;
dbObj = new AccessDB();
dbObj.Open();
bool isAdmin = false;
char ProfileType = 'U';
if (CheckBox1.Checked)
isAdmin = true;
//Fetch the row for the registeration table.
dbObj.Query = string.Format(@"Select borninto,emailID,passwd,firstName,LastName,FamilyBranch,HomePhone
,Address1,Address2,city,state,country,pincode from tbluserregisteration
where userregisterationid={0} "
, Session["UserRegID"]);
dbObj.ExecuteQuery();
emailID = (string)dbObj.Dataset.Tables[0].Rows[0][1];
string borninto = dbObj.Dataset.Tables[0].Rows[0][0].ToString();
//Add entry to the profile table and set the selectedProfileID to the new entry's ID.
if (dbObj.Dataset.Tables[0].Rows[0][0].ToString() == "B")
{
//insert into the profile table -> borninto is true
dbObj.Query = string.Format(@"insert into tbluserprofile (firstname,lastname,familyBranch,homephone,address1,address2,city
,state,country,pincode,createddate,isactive) values ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}',curdate(),1)"
, dbObj.Dataset.Tables[0].Rows[0][3], dbObj.Dataset.Tables[0].Rows[0][4], dbObj.Dataset.Tables[0].Rows[0][5]
, dbObj.Dataset.Tables[0].Rows[0][6], dbObj.Dataset.Tables[0].Rows[0][7], dbObj.Dataset.Tables[0].Rows[0][8]
, dbObj.Dataset.Tables[0].Rows[0][9], dbObj.Dataset.Tables[0].Rows[0][10], dbObj.Dataset.Tables[0].Rows[0][11]
, dbObj.Dataset.Tables[0].Rows[0][12]);
dbObj.ExecuteNonQuery();
//fetch the last inserted userprofileid from tbluserprofile
dbObj.Dataset.Reset();
dbObj.Query = "select max(userprofileid) from tbluserprofile";
dbObj.ExecuteQuery();
Session.Add("SelectedProfileID", dbObj.Dataset.Tables[0].Rows[0][0]);
}
else
{
//insert into the spouse table when borninto is false.
dbObj.Query = string.Format(@"insert into tblspouse (firstname,lastname,mobilephone,emailaddress,activationdate,activatedby,isactive) values ('{0}','{1}','{2}','{3}',curdate(),'{4}',1)"
, dbObj.Dataset.Tables[0].Rows[0][3], dbObj.Dataset.Tables[0].Rows[0][4], dbObj.Dataset.Tables[0].Rows[0][6]
, dbObj.Dataset.Tables[0].Rows[0][1],Session["UserLogin"]);
dbObj.ExecuteNonQuery();
//fetchin the spouseid field is an issue here ...
//fetch the last inserted userprofileid from tbluserprofile
dbObj.Dataset.Reset();
dbObj.Query = "select max(spid) from tblspouse";
dbObj.ExecuteQuery();
Session.Add("SelectedProfileID", dbObj.Dataset.Tables[0].Rows[0][0]);
ProfileType = 'S';
}
//Fetch the row for the registeration table.
dbObj.Dataset.Reset();
dbObj.Query = string.Format(@"Select borninto,emailID,passwd,firstName,LastName,FamilyBranch,HomePhone
,Address1,Address2,city,state,country,pincode from tbluserregisteration
where userregisterationid={0} "
, Session["UserRegID"]);
dbObj.ExecuteQuery();
//insert a row into the tbluserlogin table.Link the profile ID to the Login table.
string temp = string.Format(@"Insert into tbluserlogin (userprofileid,profileType,emailaddress,passwd,
creationdate,updationdate,updatedby,activationdate,activatedby
,isadmin) values({0},'{6}','{1}',(select passwd as pass from tbluserregisteration where userregisterationid = {5}),curdate(),curdate(),'{2}',curdate(),'{3}',{4})",
Session["SelectedProfileID"], dbObj.Dataset.Tables[0].Rows[0][1]
, Session["UserLogin"], Session["UserLogin"], isAdmin, Session["UserRegID"], ProfileType);
dbObj.Query = temp;
dbObj.ExecuteNonQuery();
isAdmin = false;
if (borninto == "B")
{
if (RadioButtonList1.Items[0].Selected)
{
//update isactive,updation date,updated by in tbluserprofile.
dbObj.Query = string.Format(@"Update tbluserprofile set Isactive=true,createddate=curdate()
,updateddate=curdate(),updatedby='{0}',firstName='{2}',lastName='{3}',FamilyBranch='{4}',HomePhone='{5}'
,Address1='{6}',Address2='{7}',city='{8}',state='{9}',country='{10}',pincode='{11}' where UserProfileID = {1}"
, Session["UserLogin"], Session["SelectedProfileID"], dbObj.Dataset.Tables[0].Rows[0][2]
, dbObj.Dataset.Tables[0].Rows[0][3], dbObj.Dataset.Tables[0].Rows[0][4], dbObj.Dataset.Tables[0].Rows[0][5]
, dbObj.Dataset.Tables[0].Rows[0][6], dbObj.Dataset.Tables[0].Rows[0][7], dbObj.Dataset.Tables[0].Rows[0][8]
, dbObj.Dataset.Tables[0].Rows[0][9], dbObj.Dataset.Tables[0].Rows[0][10], dbObj.Dataset.Tables[0].Rows[0][11]);
dbObj.ExecuteNonQuery();
}
else if (RadioButtonList1.Items[1].Selected)
{
//update isactive,updation date,updated by in tbluserprofile.
dbObj.Query = string.Format(@"Update tbluserprofile set Isactive=true,createddate=curdate()
,updateddate=curdate(),updatedby='{0}' where UserProfileID = {1}"
, Session["UserLogin"], Session["SelectedProfileID"]);
dbObj.ExecuteNonQuery();
}
}
else if (borninto == "M")
{
if (RadioButtonList1.Items[0].Selected)
{
//update isactive,updation date,updated by in tbluserprofile.
dbObj.Query = string.Format(@"Update tblspouse set Isactive=true
,updateddate=curdate(),updatedby='{0}',firstName='{2}',lastName='{3}',mobilePhone='{4}'
emailID='{5}' where UserProfileID = {1}"
, Session["UserLogin"], Session["SelectedProfileID"], dbObj.Dataset.Tables[0].Rows[0][2]
, dbObj.Dataset.Tables[0].Rows[0][3], dbObj.Dataset.Tables[0].Rows[0][5], dbObj.Dataset.Tables[0].Rows[0][0]
);
dbObj.ExecuteNonQuery();
}
else if (RadioButtonList1.Items[1].Selected)
{
//update isactive,updation date,updated by in tbluserprofile.
dbObj.Query = string.Format(@"Update tblspouse set Isactive=true,activationdate=curdate(),activatedby = '{0}'
,updateddate=curdate(),updatedby='{0}' where UserProfileID = {1}"
, Session["UserLogin"], Session["SelectedProfileID"]);
dbObj.ExecuteNonQuery();
}
}
dbObj.Dataset.Reset();
dbObj.Query = string.Format(@"select emailID from tbluserregisteration where userregisterationid={0} "
, Session["UserRegID"]);
dbObj.ExecuteQuery();
activatedUser = dbObj.Dataset.Tables[0].Rows[0][0].ToString();
//delete entry from the registeration table.
dbObj.Query = string.Format(@"delete from tbluserregisteration
where userregisterationid={0} "
, Session["UserRegID"]);
dbObj.ExecuteNonQuery();
}
public PayUpKeepAction(AccessDB d, int y, int s, string u = "stu")
: base(d,y,s,u)
{
}
private void BindData()
{
string spouseQuery = "Select 'SProfile.aspx' Page, s.SpID UserProfileID,s.FirstName,s.LastName,s.PreferredName,l.EmailAddress,u.FamilyBranch,s.MobilePhone,u.Address1, u.Address2,u.City,u.State,u.Country from tblspouse s left outer join tbluserlogin l on s.SpID=l.UserProfileID left outer join tbluserprofile u on s.SpouseID=u.UserProfileID where ";
AccessDB dbObj = new AccessDB();
dbObj.Open();
dbObj.Query = "Select 'ViewProfile.aspx' Page,u.UserProfileID UserProfileID,u.FirstName,u.LastName,u.PreferredName,l.EmailAddress,u.FamilyBranch,u.HomePhone,u.Address1, u.Address2,u.City,u.State,u.Country from tbluserprofile u left outer join tbluserlogin l on u.UserProfileID=l.UserProfileID where ";
string searchOperator = rdlMatchOption.SelectedValue;
if (ddlFamilyBranch.SelectedValue != string.Empty)
{
dbObj.Query += string.Format(" FamilyBranch= '{0}' {1}"
, ddlFamilyBranch.SelectedValue, searchOperator);
spouseQuery += string.Format(" FamilyBranch= '{0}' {1}"
, ddlFamilyBranch.SelectedValue, searchOperator);
}
if (txtName.Text != string.Empty)
{
dbObj.Query += string.Format(" (u.FirstName like '%{0}%' OR u.LastName like '%{0}%' OR u.PreferredName like '%{0}%') {1}"
, txtName.Text, searchOperator);
spouseQuery += string.Format(" (s.FirstName like '%{0}%' OR s.LastName like '%{0}%' OR s.PreferredName like '%{0}%') {1}"
, txtName.Text, searchOperator);
}
if (txtPlace.Text != string.Empty)
{
dbObj.Query += string.Format(" (Country like '%{0}%' OR State like '%{0}%' OR City like '%{0}%' OR Address1 like '%{0}%' OR Address2 like '%{0}%') {1}", txtPlace.Text, searchOperator);
spouseQuery += string.Format(" (Country like '%{0}%' OR State like '%{0}%' OR City like '%{0}%' OR Address1 like '%{0}%' OR Address2 like '%{0}%') {1}", txtPlace.Text, searchOperator);
}
if (dbObj.Query.EndsWith(searchOperator))
dbObj.Query = dbObj.Query.Substring(0, dbObj.Query.Length - searchOperator.Length);
else
dbObj.Query = dbObj.Query.Substring(0, dbObj.Query.Length - 6);
if (spouseQuery.EndsWith(searchOperator))
spouseQuery = spouseQuery.Substring(0, spouseQuery.Length - searchOperator.Length);
else
spouseQuery = spouseQuery.Substring(0, spouseQuery.Length - 6);
dbObj.Query = dbObj.Query + " union " + spouseQuery;
dbObj.ExecuteQuery();
grdSearch.DataSource = dbObj.Dataset;
grdSearch.DataBind();
//setting the color for the ppl who have passed away
dbObj.Dataset.Reset();
dbObj.Query = "select isdead from tbluserprofile where";
searchOperator = rdlMatchOption.SelectedValue;
if (ddlFamilyBranch.SelectedValue != string.Empty)
{
dbObj.Query += string.Format(" FamilyBranch= '{0}' {1}"
, ddlFamilyBranch.SelectedValue, searchOperator);
}
if (txtName.Text != string.Empty)
{
dbObj.Query += string.Format(" (FirstName like '%{0}%' OR LastName like '%{0}%' OR PreferredName like '%{0}%') {1}"
, txtName.Text, searchOperator);
}
if (txtPlace.Text != string.Empty)
{
dbObj.Query += string.Format(" (Country like '%{0}%' OR State like '%{0}%' OR City like '%{0}%' OR Address1 like '%{0}%' OR Address2 like '%{0}%') {1}", txtPlace.Text, searchOperator);
}
if (dbObj.Query.EndsWith(searchOperator))
dbObj.Query = dbObj.Query.Substring(0, dbObj.Query.Length - searchOperator.Length);
else
dbObj.Query = dbObj.Query.Substring(0, dbObj.Query.Length - 6);
dbObj.ExecuteQuery();
for (int i = 0; i < dbObj.Dataset.Tables[0].Rows.Count; i++)
{
if ((short)dbObj.Dataset.Tables[0].Rows[i][0] == 1)
gridCol.Add(i, Color.Red);
else
gridCol.Add(i, Color.Black);
}
if (grdSearch.Rows.Count > 0)
Label2.Visible = true;
else
Label2.Visible = false;
}
