public static string FillComboGrid(string SQL)
{
if (HttpContext.Current.Session["UserName"] == null)
{
return "Error: You are not logged-in.";
}
DBClass db = new DBClass("Accounts");
db.Con.Open();
db.Com.CommandText = SQL;
SqlDataAdapter da = new SqlDataAdapter(db.Com);
DataTable dt = new DataTable();
da.Fill(dt);
System.Web.Script.Serialization.JavaScriptSerializer serializer = new System.Web.Script.Serialization.JavaScriptSerializer();
List<Dictionary<string, object>> rows = new List<Dictionary<string, object>>();
Dictionary<string, object> row;
foreach (DataRow dr in dt.Rows)
{
row = new Dictionary<String, Object>();
foreach (DataColumn col in dt.Columns)
{
row.Add(col.ColumnName, dr[col]);
}
rows.Add(row);
}
db.Con.Close();
db = null;
return String.Format("{{\"total\":{0},\"rows\":{1}}}", dt.Rows.Count, serializer.Serialize(rows));
}
public static string GetRow(string RowId)
{
if (HttpContext.Current.Session["UserName"] == null)
{
return "Error: You are not logged-in.";
}
DBClass db = new DBClass("Muntazm");
db.Con.Open();
db.Com.CommandText = "SELECT [CurCode],[CurDesc] FROM [Currencies] WHERE [CurCode] ='"+ RowId +"'";
SqlDataReader dr = db.Com.ExecuteReader();
JavaScriptSerializer serailizer = new JavaScriptSerializer();
SurchResult SurchResult = new SurchResult();
if (dr.HasRows)
{
while (dr.Read())
{
SurchResult.SurCode = dr[0].ToString();
SurchResult.SurDescription = dr[1].ToString();
//curriencies.DecimalDescription = dr[2].ToString();
//curriencies.Status = dr[3].ToString();
}
}
if (dr.IsClosed == false)
dr.Close();
db.Con.Close();
db = null;
return serailizer.Serialize(SurchResult);
}
/// <summary>
/// 根据控件生成Insert的Commond对象，返回新增语句的主键
/// </summary>
/// <param name="FormControl">需要组成SQL语句的控件</param>
/// <param name="Table">表名</param>
/// <param name="TableId">更新的ID</param>
/// <param name="db">需要生成Commond的DBHelper</param>
/// <returns>更新数据的键值</returns>
public static void GetInsertCommond(Control FormControl, string Table, string TableId, DBClass.DBHelper db)
{
string KeyValue = string.Empty;
StringBuilder FieldString = new StringBuilder();
StringBuilder ValueString = new StringBuilder();
foreach (Control ControlItem in FormControl.Controls)
{
if (ControlItem.GetType() == typeof(TextBox) || ControlItem.GetType() == typeof(RichTextBox))
{
if (!string.IsNullOrEmpty(ControlItem.Text))
{
if (ControlItem.Name == TableId)
{
KeyValue = ControlItem.Text;
}
FieldString.AppendFormat("{0},", ControlItem.Name);
ValueString.AppendFormat("@{0},", ControlItem.Name);
db.AddInParameter(string.Format("@{0}", ControlItem.Name), DbType.AnsiString, ControlItem.Text);
}
}
}
string SQLString = String.Format("Insert into {0} ({1}) values ({2})",
Table,
FieldString.ToString(0, FieldString.Length - 1),
ValueString.ToString(0, ValueString.Length - 1));
db.SetSqlStringCommond(SQLString);
}
public static string GetAccounts(string filter = "")
{
if (HttpContext.Current.Session["UserName"] == null)
{
return "Error: You are not logged-in.";
}
DBClass db = new DBClass("Accounts");
db.Con.Open();
db.Com.CommandText = "SELECT [AccountCode],[Description] FROM [Account] " + filter;
SqlDataAdapter da = new SqlDataAdapter(db.Com);
DataTable dt = new DataTable();
da.Fill(dt);
JavaScriptSerializer serializer = new JavaScriptSerializer();
List<Dictionary<string, object>> rows = new List<Dictionary<string, object>>();
Dictionary<string, object> row;
foreach (DataRow dr in dt.Rows)
{
row = new Dictionary<String, Object>();
foreach (DataColumn col in dt.Columns)
{
row.Add(col.ColumnName, dr[col]);
}
rows.Add(row);
}
db.Con.Close();
db = null;
return String.Format("{{\"total\":{0},\"rows\":{1}}}", dt.Rows.Count, serializer.Serialize(rows));
//return serializer.Serialize(rows);
}
/// <summary>
/// Constructor injection of the Dao object
/// </summary>
/// <param name="dbClass"></param>
public PlainObject(DBClass dbClass)
{
if (dbClass == null)
throw new ArgumentNullException("DBClass is null");
this.dbClass = dbClass;
}
public static string FillTable(string filter)
{
if (HttpContext.Current.Session["UserName"] == null)
{
return "Error: You are not logged-in.";
}
DBClass db = new DBClass("Muntazm");
db.Con.Open();
/*if(String.IsNullOrEmpty(filter))
db.Com.CommandText = "SELECT TOP 8 [UserID],[UserName],[Status] FROM [Users]";
else*/
db.Com.CommandText = "SELECT [CurCode],[CurDesc] FROM [Currencies] WHERE [CurDesc] LIKE '%" + filter + "%'";
SqlDataReader dr = db.Com.ExecuteReader();
string tableRows = "";
if (dr.HasRows)
{
while (dr.Read())
{
tableRows = tableRows + String.Format("<tr><td>{0}</td><td>{1}</td></tr>", dr[0].ToString(), dr[1].ToString());
}
}
db.Con.Close();
db = null;
return tableRows;
}
public static string GetAttachments(string BranchCode, string Year, string Month, string VoucherTypeCode, string VoucherCode)
{
DBClass db = new DBClass("Accounts");
db.Con.Open();
db.Com.CommandText = String.Format("SELECT AttachID,FileName FROM Attachments WHERE CoID='{0}' AND BranchCode='{1}' AND Year='{2}' AND Month='{3}' AND VochType='{4}' AND VochNo='{5}' AND Status=1", HttpContext.Current.Session["CompanyId"], BranchCode, Year, Month, VoucherTypeCode, VoucherCode);
SqlDataAdapter da = new SqlDataAdapter(db.Com);
DataTable dt = new DataTable();
da.Fill(dt);
JavaScriptSerializer serializer = new JavaScriptSerializer();
List<Dictionary<string, object>> rows = new List<Dictionary<string, object>>();
Dictionary<string, object> row;
foreach (DataRow dr in dt.Rows)
{
row = new Dictionary<String, Object>();
foreach (DataColumn col in dt.Columns)
{
row.Add(col.ColumnName, dr[col]);
}
rows.Add(row);
}
db.Con.Close();
db = null;
return serializer.Serialize(rows);
}
/// <summary>
/// 写入日志
/// </summary>
/// <param name="UserId">用户ID</param>
/// <param name="WorkStation">主机名</param>
/// <param name="ProgramId">程序模块名</param>
/// <param name="OptType">操作类型</param>
/// <param name="KeyValue">操作键值</param>
/// <param name="db">需要生成Commond的DBHelper</param>
public static void GetSysLogCommond(string UserId, string WorkStation, string ProgramId, string OptType, string KeyValue, DBClass.DBHelper db)
{
db.SetSqlStringCommond("Insert Into Wms_SysLog (UserId, WorkStation, ProgramId, OptTime, OptType, KeyValue) Values (@UserId, @WorkStation, @ProgramId, @OptTime, @OptType, @KeyValue)");
db.AddInParameter("@UserId", DbType.AnsiString, UserId);
db.AddInParameter("@WorkStation", DbType.AnsiString, WorkStation);
db.AddInParameter("@ProgramId", DbType.AnsiString, ProgramId);
db.AddInParameter("@OptTime", DbType.AnsiString, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
db.AddInParameter("@OptType", DbType.AnsiString, OptType);
db.AddInParameter("@KeyValue", DbType.AnsiString, KeyValue);
}
public void FillCombo()
{
DBClass db = new DBClass("Accounts");
db.Con.Open();
db.Com.CommandText = "Select [CompCode], [CompName] from [Companies]";
SqlDataAdapter da = new SqlDataAdapter(db.Com);
DataTable dt = new DataTable();
da.Fill(dt);
rptCompanies.DataSource = dt;
rptCompanies.DataBind();
db.Con.Close();
db = null;
}
public static string FillExpGrid(int PageNumber, int PageSize, string filter)
{
if (HttpContext.Current.Session["UserName"] == null)
{
return "Error: You are not logged-in.";
}
DBClass db = new DBClass("SCM");
db.Con.Open();
if (String.IsNullOrEmpty(filter))
db.Com.CommandText = "SELECT [AGglCode], [FOglCode], [SDglCode], [COGSglCode] FROM [costsetupOverheadMainheadGLCode] ORDER BY [id]";
else
db.Com.CommandText = "SELECT [AGglCode], [FOglCode], [SDglCode], [COGSglCode] FROM [costsetupOverheadMainheadGLCode] where Branchid = '"+filter+"' ORDER BY [id]";
SqlDataAdapter da = new SqlDataAdapter(db.Com);
DataTable dt = new DataTable();
da.Fill(dt);
DataTable paggedtable = new DataTable();
paggedtable = dt.Clone();
int initial = (PageNumber - 1) * PageSize + 1;
int last = initial + PageSize;
for (int i = initial - 1; i < last - 1; i++)
{
if (i >= dt.Rows.Count)
{
break;
}
paggedtable.ImportRow(dt.Rows[i]);
}
System.Web.Script.Serialization.JavaScriptSerializer serializer = new System.Web.Script.Serialization.JavaScriptSerializer();
List<Dictionary<string, object>> rows = new List<Dictionary<string, object>>();
Dictionary<string, object> row;
foreach (DataRow dr in paggedtable.Rows)
{
row = new Dictionary<String, Object>();
foreach (DataColumn col in paggedtable.Columns)
{
row.Add(col.ColumnName, dr[col]);
}
rows.Add(row);
}
db.Con.Close();
db = null;
return String.Format("{{\"total\":{0},\"rows\":{1}}}", dt.Rows.Count, serializer.Serialize(rows));
}
public static string FillGrid(string DBName, string SqlQuery)
{
if (HttpContext.Current.Session["UserName"] == null)
{
return "Error: You are not logged-in.";
}
DBClass db = new DBClass(DBName);
db.Con.Open();
db.Com.CommandText = SqlQuery;
string Json = (new System.Web.Script.Serialization.JavaScriptSerializer()).Serialize(GlobalFunctions.GetRowsForJson(db.Com));
db.Con.Close();
db = null;
return Json;
}
public static string DeleteRow(string RowId)
{
if (HttpContext.Current.Session["UserName"] == null)
{
return "Error: You are not logged-in.";
}
DBClass db = new DBClass("SCM");
db.Con.Open();
db.Com.CommandText = String.Format("DELETE FROM [setupBankAccount] WHERE [id]='{0}'", RowId);
db.Com.ExecuteNonQuery();
db.Con.Close();
db = null;
return "Record deleted successfully.";
}
protected void CrystalReportViewer1_Init(object sender, EventArgs e)
{
string DatabaseName = HttpContext.Current.Session["DatabaseName"].ToString();
DBClass db = new DBClass(DatabaseName);
string SqlQuery = HttpContext.Current.Session["SqlQuery"].ToString();
string ReportFileName = HttpContext.Current.Session["ReportFileName"].ToString();
da = new SqlDataAdapter(SqlQuery, db.Con);
dt = new DataTable();
da.Fill(dt);
reportDocument = new ReportDocument();
reportDocument.Load(ReportFileName);
reportDocument.SetDataSource(dt);
reportDocument.ReadRecords();
CrystalReportViewer1.ReportSource = reportDocument;
}
public void TestDBClass()
{
// Test both concrete classes
DBClass dbClass = new DBClass();
PlainObject po = new PlainObject(dbClass);
po.Id = 1;
po.Name = "Homer";
// Verify that the object saved successfully
Assert.IsTrue(po.SaveToDb());
// Verify that the object added is there and is the correct object
Assert.AreEqual(dbClass.DataBase.Count, 1);
Assert.ReferenceEquals(dbClass.DataBase[1], po);
}
public static string FillGrid(int PageNumber, int PageSize, string filter)
{
if (HttpContext.Current.Session["UserName"] == null)
{
return "Error: You are not logged-in.";
}
DBClass db = new DBClass("SCM");
db.Con.Open();
db.Com.CommandText = "SELECT [id] , [Description], MarketingOverhead, VariableExpense FROM [CostsetupItem] " + filter + " Order By [id] ";
SqlDataAdapter da = new SqlDataAdapter(db.Com);
DataTable dt = new DataTable();
da.Fill(dt);
DataTable paggedtable = new DataTable();
paggedtable = dt.Clone();
int initial = (PageNumber - 1) * PageSize + 1;
int last = initial + PageSize;
for (int i = initial - 1; i < last - 1; i++)
{
if (i >= dt.Rows.Count)
{
break;
}
paggedtable.ImportRow(dt.Rows[i]);
}
System.Web.Script.Serialization.JavaScriptSerializer serializer = new System.Web.Script.Serialization.JavaScriptSerializer();
List<Dictionary<string, object>> rows = new List<Dictionary<string, object>>();
Dictionary<string, object> row;
foreach (DataRow dr in paggedtable.Rows)
{
row = new Dictionary<String, Object>();
foreach (DataColumn col in paggedtable.Columns)
{
row.Add(col.ColumnName, dr[col]);
}
rows.Add(row);
}
db.Con.Close();
db = null;
return String.Format("{{\"total\":{0},\"rows\":{1}}}", dt.Rows.Count, serializer.Serialize(rows));
}
public static string DeleteRow(string RowId)
{
if (HttpContext.Current.Session["UserName"] == null)
{
return "Error: You are not logged-in.";
}
DBClass db = new DBClass("SCM");
db.Con.Open();
//db.Com.CommandText = String.Format("SELECT COUNT(*) FROM [CostsetupItem] WHERE [id]='{0}'", RowId);
//if ((Int32)db.Com.ExecuteScalar() > 0)
// return "Error: Catagory is In-Use.";
db.Com.CommandText = String.Format("DELETE FROM [CostsetupItem] WHERE [id]='{0}'", RowId);
db.Com.ExecuteNonQuery();
db.Con.Close();
db = null;
return "Record deleted successfully.";
}
public static string Add(string CatagoryCode, string Description, string SubHeadCode, string MarketingOverhead, string VariableExpense)
{
if (HttpContext.Current.Session["UserName"] == null)
{
return "Error: You are not logged-in.";
}
DBClass db = new DBClass("SCM");
db.Con.Open();
db.Com.CommandText = "SELECT COUNT(*) FROM [CostsetupItem] WHERE [id]='" + CatagoryCode + "' AND [Description]='" + Description + "'";
if ((Int32)db.Com.ExecuteScalar() > 0)
return "Error: Same data already exists.";
db.Com.CommandText = String.Format("INSERT INTO [CostsetupItem] ([id],[Description],[subheadid],[VariableExpense],[MarketingOverhead]) VALUES ('{0}','{1}','{2}','{3}','{4}')", CatagoryCode, Description, SubHeadCode, MarketingOverhead, VariableExpense);
db.Com.ExecuteNonQuery();
db.Con.Close();
db = null;
return "Record added successfully.";
}
public static string Add(string AccountCode,string Description,string SubHeadId,string Currency,string GLCode,string BankName,string BranchName, string AccountType,string AccountNo,string SwiftCode,string IBAN)
{
if (HttpContext.Current.Session["UserName"] == null)
{
return "Error: You are not logged-in.";
}
DBClass db = new DBClass("SCM");
db.Con.Open();
db.Com.CommandText = "SELECT COUNT(*) FROM [setupBankAccount] WHERE [id]='" + AccountCode + "' AND [Description]='" + Description + "' AND [subheadid]= '" + SubHeadId + "' AND [BankName]='" + BankName + "' AND [BranchName]= '" + BranchName + "' AND [currency]='" + Currency + "' AND [BankGlCode]= '" + GLCode + "' AND [AccountType]='" + AccountType + "' AND [AccountNo]='" + AccountNo + "' AND [SwiftCode]='" + SwiftCode + "' AND [iban]= '" + IBAN + "'";
if ((Int32)db.Com.ExecuteScalar() > 0)
return "Error: Same data already exists.";
db.Com.CommandText = String.Format("INSERT INTO [setupBankAccount] ([id],[Description],[subheadid],[currency],[BankGlCode],[BankName],[BranchName],[AccountType],[AccountNo],[SwiftCode],[iban]) VALUES ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}')", AccountCode,Description,SubHeadId,Currency,GLCode, BankName, BranchName, AccountType, AccountNo, SwiftCode,IBAN);
db.Com.ExecuteNonQuery();
db.Con.Close();
db = null;
return "Record added successfully.";
}
/// <summary>
/// 根据控件返回Update的Commond对象
/// </summary>
/// <param name="FormControl">需要组成SQL语句的控件</param>
/// <param name="Table">表名</param>
/// <param name="TableId">更新的ID</param>
/// <param name="db">需要生成Commond的DBHelper</param>
/// <returns>更新数据的键值</returns>
public static void GetUpdateCommond(Control FormControl, string Table, string TableId, DBClass.DBHelper db)
{
string Where = string.Empty;
string KeyValue = string.Empty;
StringBuilder FieldString = new StringBuilder();
for (int i = 0; i < FormControl.Controls.Count; i++)
{
if (FormControl.Controls[i].GetType() == typeof(TextBox) || FormControl.Controls[i].GetType() == typeof(RichTextBox))
{
if (!string.IsNullOrEmpty(FormControl.Controls[i].Text))
{
if (FormControl.Controls[i].Name == TableId)
{
KeyValue = FormControl.Controls[i].Text;
Where = String.Format("{0}=@{0}", FormControl.Controls[i].Name);
}
else
{
FieldString.AppendFormat("{0}=@{0},", FormControl.Controls[i].Name);
}
}
}
}
string SQLString = String.Format("Update {0} set {1} Where {2}", Table, FieldString.ToString(0, FieldString.Length - 1), Where);
db.SetSqlStringCommond(SQLString);
for (int i = 0; i < FormControl.Controls.Count; i++)
{
if (FormControl.Controls[i].GetType() == typeof(TextBox) || FormControl.Controls[i].GetType() == typeof(RichTextBox))
{
if (!string.IsNullOrEmpty(FormControl.Controls[i].Text))
{
if (FormControl.Controls[i].Name == TableId)
{
db.AddInParameter(string.Format("@{0}", FormControl.Controls[i].Name), DbType.AnsiString, FormControl.Controls[i].Text);
}
else
{
db.AddInParameter(string.Format("@{0}", FormControl.Controls[i].Name), DbType.AnsiString, FormControl.Controls[i].Text);
}
}
}
}
}
public static string Add(string VoucherCode, string BranchCode, string VoucherTypeCode, string Date, string Month, string Year, string Note, float Debit, float Credit, string Rows, string Currency, float ExchangeRate, string ProjectCode)
{
DBClass db = new DBClass("Accounts");
List<TransactionData> list = GlobalFunctions.DeserializeJSONData(new TransactionData(), Rows);
db.Con.Open();
db.Com.CommandText = String.Format("DELETE FROM [Vouchers] WHERE [VoucherCode]={0} AND [Month]='{1}' AND [Year]='{2}' AND [VoucherTypeCode]='{3}'", VoucherCode, Month, Year.Trim(), VoucherTypeCode);
db.Com.ExecuteNonQuery();
db.Com.CommandText = String.Format("DELETE FROM [VoucherDetail] WHERE [VoucherCode]={0} AND [Month]='{1}' AND [Year]='{2}' AND [VoucherTypeCode]='{3}'", VoucherCode, Month, Year.Trim(), VoucherTypeCode);
db.Com.ExecuteNonQuery();
db.Com.CommandText = String.Format("INSERT INTO [Vouchers] ([VoucherCode],[BranchCode],[VoucherTypeCode],[Date],[Month],[Year],[Naration],[Currency],[ExchangeRate],[PreparedBy],[Status],[ProjectCode]) VALUES ({0},'{1}','{2}','{3}','{4}','{5}','{6}','{7}',{8},'{9}','{10}','{11}')", VoucherCode, BranchCode, VoucherTypeCode, Date, Month, Year.Trim(), Note, Currency, ExchangeRate, HttpContext.Current.Session["UserName"], "Prepared", ProjectCode);
db.Com.ExecuteNonQuery();
for (int i=0; i < list.Count; i++)
{
db.Com.CommandText = String.Format("INSERT INTO [VoucherDetail] ([VoucherCode],[BranchCode],[VoucherTypeCode],[Accounts],[Month],[Year],[Note],[Debit],[Credit]) VALUES ({0},'{1}','{2}','{3}','{4}','{5}','{6}',{7},{8})", VoucherCode, BranchCode, VoucherTypeCode, list[i].AccountCode, Month, Year.Trim(), String.IsNullOrEmpty(list[i].Note) ? Note : list[i].Note, String.Format("{0} * '{1}", Convert.ToDouble(list[i].Debit), ExchangeRate.ToString()), String.Format("{0} * {1}", Convert.ToDouble(list[i].Credit), ExchangeRate.ToString()));
db.Com.ExecuteNonQuery();
}
return "Record Added Successfully.";
}
public static string Add(string id, string description, string branchID, string AGglCode, string FOglCode, string SDglCode, string COGSglCOde)
{
if (HttpContext.Current.Session["UserName"] == null)
{
return "Error: You are not logged-in.";
}
DBClass db = new DBClass("SCM");
db.Con.Open();
db.Com.CommandText = "SELECT COUNT(*) FROM [SetupLocation] WHERE [id]='" + id + "' AND [description]='" + description + "'";
if ((Int32)db.Com.ExecuteScalar() > 0)
return "Error: Same data already exists.";
db.Com.CommandText = String.Format("INSERT INTO [costsetupItemMainhead] ([id],[description]) VALUES ('{0}','{1}')", id, description);
db.Com.ExecuteNonQuery();
db.Com.CommandText = String.Format("INSERT INTO [costsetupOverheadMainheadGlCode] ([MohID],[BranchID],[AGglCode],[FOglCode],[SDglCode],[COGSglCOde]) VALUES ('{0}','{1}','{2}','{3}','{4}','{5}')", id, branchID, AGglCode, FOglCode, SDglCode, COGSglCOde);
db.Com.ExecuteNonQuery();
db.Con.Close();
db = null;
return "Record added successfully.";
}
public static string DoLogin(string UserName, string Password, string CompanyId, string CompanyName)
{
DBClass db = new DBClass("Muntazm");
string result;
db.Con.Open();
db.Com.CommandText = "SELECT COUNT(*) FROM [Users] WHERE [UserName]='" + UserName + "' AND [Password]='" + Password + "'";
if ((Int32)db.Com.ExecuteScalar() == 1)
{
result = "You have been logged-in successfully.";
HttpContext.Current.Session["UserName"] = UserName;
HttpContext.Current.Session["CompanyId"] = CompanyId;
HttpContext.Current.Session["CompanyName"] = CompanyName;
}
else
{
result = "Error: Invalid User name or Password.";
}
db.Con.Close();
db = null;
return result;
}
public ActionResult ResetPassword(ResetPasswordViewModel model)
{
if (ModelState.IsValid)
{
using (DBClass context = new DBClass())
{
context.AddParameter("@username", Session["UserName"].ToString());
DataTable dt = context.getData("getUserRole", CommandType.StoredProcedure);
if (Convert.ToString(dt.Rows[0]["UserRole"]) == "Employee" || Convert.ToString(dt.Rows[0]["UserRole"]) == "Admin")
{
context.ExecuteNonQuery("Update Emp_Detail set Password = '******' where LoginID = '" + Session["UserName"].ToString() + "'", CommandType.Text);
}
else
{
context.ExecuteNonQuery("Update UserDetail set uPassword = '******' where UserName = '******'", CommandType.Text);
}
WebSecurity.ChangePassword(Session["UserName"].ToString(), model.CurrentPassword, model.NewPassword);
return(View("Index"));
}
}
return(View());
}
public static Item getItemByName(string name)
{
DataSet data = DBClass.SelectCommand("exec spGetItemByName '" + name + "'");
if (data.Tables[0].Rows.Count == 0)
{
return(null);
}
else
{
DataRow row = data.Tables[0].Rows[0];
Item item = new Item();
item.ItemId = Convert.ToInt16(row[0].ToString());
item.Name = row[1].ToString();
item.State = Convert.ToInt16(row[2].ToString());
item.ProductionDate = row[3].ToString();
item.CategoryId = Convert.ToInt16(row[4].ToString());
item.PublisherId = Convert.ToInt16(row[5].ToString());
item.Type = row[6].ToString();
return(item);
}
}
//获取已检数据
public string GetFQCDoneData(string keyword, string strSort, int pageSize, int curPage, out int pageCount, out int recCount, out DataSet outDataSet)
{
string result = "";
pageCount = 0;
recCount = 0;
outDataSet = new DataSet();
DBClass dbc = new DBClass();
try
{
#region SQL语句条件
string whereStr = " (ISNULL(QCResult, - 1)>0)";
if (!string.IsNullOrEmpty(keyword))
{
whereStr += " AND (MOName='" + keyword + "' OR BillNo='" + keyword + "' OR ProductName='" + keyword + "' OR CustomerName='" + keyword + "')";
}
if (strSort == "")
{
strSort = "CreateDate desc";
}
#endregion
//SQL语句数组，组合分页语句
string[] sql = new string[] {
/* select */ @"*",
/* from */ @"V_GetFQCPackDoneCheck",
/* where */ whereStr,
/* order by */ strSort
};
outDataSet = dbc.getPageDataSet(sql, curPage, pageSize, out pageCount, out recCount);
result = "0|获取成功";
}
catch (Exception e)
{
result = "1|" + e.Message;
}
return(result);
}
public Boolean InsertSection_master(FormCollection f)
{
GenHelper g = new GenHelper();
String Sqltext = "insert into Section_master (sectionid,sectionname) values(@sectionid,@sectionname)";
SqlConnection con = DBClass.mycon();
SqlCommand cmd = new SqlCommand(Sqltext, con);
cmd.Parameters.AddWithValue("@sectionid", g.GetNextId("Section_master", "sectionid"));
cmd.Parameters.AddWithValue("@sectionname", f["sectionname"]);
int ab = cmd.ExecuteNonQuery();
con.Close();
if (ab > 0)
{
return(true);
}
else
{
return(false);
}
}
public static string DoLogin(string UserName, string Password, string CompanyId, string CompanyName)
{
DBClass db = new DBClass("Muntazm");
string result;
db.Con.Open();
db.Com.CommandText = "SELECT COUNT(*) FROM [Users] WHERE [UserName]='" + UserName + "' AND [Password]='" + Password + "'";
if ((Int32)db.Com.ExecuteScalar() == 1)
{
result = "You have been logged-in successfully.";
HttpContext.Current.Session["UserName"] = UserName;
HttpContext.Current.Session["CompanyId"] = CompanyId;
HttpContext.Current.Session["CompanyName"] = CompanyName;
}
else
{
result = "Error: Invalid User name or Password.";
}
db.Con.Close();
db = null;
return(result);
}
public static string GetMaxId()
{
if (HttpContext.Current.Session["UserName"] == null)
{
return("Error: You are not logged-in.");
}
DBClass db = new DBClass("SCM");
string Id;
db.Con.Open();
db.Com.CommandText = String.Format("SELECT CAST(isNULL(MAX(CAST([id] AS INT)),0)+1 AS VARCHAR(2)) AS Id FROM [costsetupItemMainhead]");
Id = (string)db.Com.ExecuteScalar();
if (Id.Length == 1)
{
Id = "0" + Id;
}
db.Com.ExecuteNonQuery();
db.Con.Close();
db = null;
return(Id);
}
public ActionResult SaveNewWorkingHour(DMNewWorkingHour data)
{
HttpCookie myCookie = new HttpCookie("UInfo");
myCookie = Request.Cookies["UInfo"];
if (myCookie != null)
{
try
{
DBA = new DBClass();
objTools = new Utility();
Hashtable hst = new Hashtable();
DataModel.DMUsersLoginDetails DL = objTools.GetClientLoginDetails(myCookie.Value.ToString());
hst.Add("@ClientID", Convert.ToInt16(DL.ClientID));
hst.Add("@BranchID", data.Branch);
hst.Add("@TimeGroupName", data.TimeGroupName);
hst.Add("@CurrentIn", data.CurrentIn);
hst.Add("@MaxIn", data.MaxIn);
hst.Add("@MinIn", data.MinIn);
hst.Add("@CurrentOut", data.CurrentOut);
hst.Add("@MaxOut", data.MaxOut);
hst.Add("@MinOut", data.MinOut);
hst.Add("@UserName", DL.FullName);
DBA.ExecSP("[SP_AttendanceTimeGroup_Insert]", hst);
return(Json(new { isSuccess = true, msg = string.Format("Save") }, JsonRequestBehavior.AllowGet));
}
catch (Exception ex)
{
return(Json(new { isSuccess = false, msg = string.Format("Failed!" + ex.Message.ToString()), url = "/" }, JsonRequestBehavior.AllowGet));
}
}
else
{
return(RedirectPermanent("/"));
}
}
public DataTable GetPLAccount()
{
String SumQry = "select (select sum(clbal) from mytrialbal t1 where parentid=4) Revenue,(select sum(clbal) from mytrialbal t2 where parentid=5) Expenses";
DataTable SumTbl = DBClass.GetData(SumQry);
Double pl = Convert.ToDouble(SumTbl.Rows[0]["Revenue"]) - Math.Abs(Convert.ToDouble(SumTbl.Rows[0]["expenses"]));
if (pl < 0)
{
String InsQry = "insert into mytrialbal(accountnumber,accountname,parentid,clbal) values(499999,'Loss For The Year',4," + Math.Abs(pl).ToString() + ")";
DBClass.NonQuery(InsQry);
}
else
{
String InsQry = "insert into mytrialbal(accountnumber,accountname,parentid,clbal) values(599999,'Profit For The Year',5," + Math.Abs(pl).ToString() + ")";
DBClass.NonQuery(InsQry);
}
String PlQry = "delete from receipts;";
PlQry += "insert into receipts(sno,accountnumber,accountname,amount) ";
PlQry += "(select ROW_NUMBER() OVER (ORDER BY accountnumber) As sno,accountnumber,accountname,sum(clbal)";
PlQry += " from mytrialbal where clbal!=0 and parentid=4 group by accountnumber,accountname);";
PlQry += "delete from payments;";
PlQry += "insert into payments(sno,accountnumber,accountname,amount) ";
PlQry += "(select ROW_NUMBER() OVER (ORDER BY accountnumber) As sno,accountnumber,accountname,abs(sum(clbal))";
PlQry += " from mytrialbal where clbal!=0 and parentid=5 group by accountnumber,accountname)";
String DispQry;
DBClass.NonQuery(PlQry);
if (pl > 0)
{
DispQry = "SELECT r.accountnumber,r.accountname,r.amount,p.accountnumber,p.accountname,p.amount FROM receipts AS r right JOIN dbo.payments AS p ON r.sno = p.sno";
}
else
{
DispQry = "SELECT r.accountnumber,r.accountname,r.amount,p.accountnumber,p.accountname,p.amount FROM receipts AS r left JOIN dbo.payments AS p ON r.sno = p.sno";
}
return(DBClass.GetData(DispQry));
}
public ActionResult AddDriveUnit(int id, FormCollection form)
{
int fileID = id;
try
{
using (DBClass context = new DBClass())
{
context.AddParameter("@MachineName", form["TrainName"]);
context.AddParameter("@MachineOrientation", form["MOType"]);
context.AddParameter("@MountingType", form["MountingType"]);
context.AddParameter("@UserName", Session["UserName"].ToString());
context.AddParameter("@DriveUnitName", form["DEUnitType"]);
context.AddParameter("@RPM", form["DURPM"]);
context.AddParameter("@Ber_Lubrication", form["BerLubrication"]);
context.AddParameter("@Ber_Manufacture", form["bearingMake"]);
context.AddParameter("@Ber_NumberDE", form["bearingNoDE"]);
context.AddParameter("@Ber_NumberNDE", form["bearingNoNDE"]);
context.AddParameter("@NoOfPoles", form["NoOfPoles"]);
context.AddParameter("@Point_Count", form["Point_Count_DE"]);
context.AddParameter("@FileID", fileID);
if (context.ExecuteNonQuery("AddDriveUnit", CommandType.StoredProcedure) > 0)
{
ModelState.Clear();
//TempData["Status"] = "Drive Unit Added successfully";
return(RedirectToAction("MachineDetail", "UploadDownload"));
// return status;
}
else
{
ModelState.AddModelError("", "An unknown error occurred. Please verify your entry and try again. If the problem persists, please contact your system administrator.");
}
}
}
catch { }
return(View());
//return status;
}
private static DataTable pSelect_Catalog()
{
DataTable dataTable = new DataTable();
DataSet ds = new DataSet();
try
{
// อ่านค่าจาก Store pSelect_Catalog
SqlParameterCollection param = new SqlCommand().Parameters;
ds = new DBClass().SqlExcSto("pSelect_Catalog", "DbSet", param);
dataTable = ds.Tables[0];
}
catch (SqlException)
{
dataTable = null;
}
catch (Exception)
{
dataTable = null;
}
return(dataTable);
}
public static Member getMemberByUserId(int userId)
{
DataSet data = DBClass.SelectCommand("exec spGetMemberByUserId " + userId);
if (data.Tables[0].Rows.Count == 0)
{
return(null);
}
else
{
DataRow row = data.Tables[0].Rows[0];
Member member = new Member();
member.MemberId = Convert.ToInt32(row[0]);
member.FacultyName = row[1].ToString();
member.MemberType = row[2].ToString();
member.UserId = Convert.ToInt32(row[3]);
member.IsActive = Convert.ToBoolean(row[4]);
return(member);
}
}
public Boolean InsertUser_types(FormCollection f)
{
GenHelper g = new GenHelper();
String Sqltext = "insert into User_types (usertypeid,usertypename) values(@usertypeid,@usertypename)";
SqlConnection con = DBClass.mycon();
SqlCommand cmd = new SqlCommand(Sqltext, con);
cmd.Parameters.AddWithValue("@usertypeid", g.GetNextId("User_types", "usertypeid"));
cmd.Parameters.AddWithValue("@usertypename", f["usertypename"]);
int ab = cmd.ExecuteNonQuery();
con.Close();
if (ab > 0)
{
return(true);
}
else
{
return(false);
}
}
private void ShowData1()
{
var sql = "SELECT [ST_ID],S.[ST_NAME],U.ST_NAME,[ST_IN],[ST_PRICE],S.[HIDE]"
+ " FROM[COS_STOCK] S LEFT JOIN COS_UNIT U"
+ " ON S.ST_UNIT = U.ST_UNIT ORDER BY S.ST_NAME ASC";
var dt = new DBClass().SqlGetData(sql);
dgv1.DataSource = dt;
dgv1.Columns[0].HeaderText = "รหัส";
dgv1.Columns[1].HeaderText = "ชื่ออะไหล่";
dgv1.Columns[2].HeaderText = "หน่วย";
dgv1.Columns[3].HeaderText = "จำนวนในสต๊อก";
dgv1.Columns[4].HeaderText = "ราคา";
dgv1.Columns[5].HeaderText = "สถานะ";
dgv1.Columns[0].Width = 40;
dgv1.Columns[1].Width = 150;
dgv1.Columns[2].Width = 100;
dgv1.Columns[3].Width = 100;
dgv1.Columns[4].Width = 100;
dgv1.Columns[5].Width = 100;
}
protected void Page_Load(object sender, EventArgs e)
{
DBClass _db = new DBClass();
DataRow rUser = _db.get_Info_user_cms(ReadCookie("adminUserName"));
if (rUser != null)
{
if (BaseView.GetStringFieldValue(rUser, "isAdmin").ToLower() != "true")
{
Response.Redirect("~/admin-us/account/login.aspx");
}
}
if (!IsPostBack)
{
GetHistory();
if (!String.IsNullOrEmpty(Request.QueryString["user"]))
{
btnXEM.Visible = true;
btnXEM.PostBackUrl = "~/admin-us/quan-ly-tai-khoan/info-acc.aspx?user="******"user"];
}
}
}
private SelectList GetCountry()
{
using (DBClass context = new DBClass())
{
try
{
dtList.Clear();
dtList = context.getData("getCountry", CommandType.StoredProcedure);
for (int i = 0; i < dtList.Rows.Count; i++)
{
Country.Add(new SelectListItem {
Text = Convert.ToString(dtList.Rows[i]["CountryName"]), Value = Convert.ToString(dtList.Rows[i]["CountryID"])
});
}
}
catch (Exception e)
{
string msg = e.Message;
}
return(new SelectList(Country, "Value", "Text", "id"));
}
}
private void getBaiMoi()
{
DBClass _db = new DBClass();
string html = "";
string sqlCMD = "select top 10 * from news order by id desc";
DataTable dt = _db.sqlGetData(sqlCMD);
foreach (DataRow row in dt.Rows)
{
string hinh = "../uploadFile/postImages/" + BaseView.GetStringFieldValue(row, "HinhAnh");
html += "<div class='item-bai-moi'>";
html += "<div class='img-c'>";
html += "<a '../" + BaseView.GetStringFieldValue(row, "url") + "' title=' " + BaseView.GetStringFieldValue(row, "TieuDe") + "'>";
html += "<img src='" + hinh + "' alt='" + BaseView.GetStringFieldValue(row, "TieuDe") + "' height='70px' width='70px' /></a></div>";
html += "<div class='table-c'><table><tr><td>";
string tieude = BaseView.GetStringFieldValue(row, "TieuDe");
if (tieude.Length > 75)
{
tieude = tieude.Substring(0, 75) + "...";
}
html += "<h3><a href='../" + BaseView.GetStringFieldValue(row, "url") + "' title=' " + BaseView.GetStringFieldValue(row, "TieuDe") + "'>" + tieude + "</a></h3>";
html += "</td> </tr>";
/*
* html+="<tr> <td style='height: 23px'>";
* string mota = BaseView.GetStringFieldValue(row, "tomtat");
* if (mota.Length > 50)
* mota = mota.Substring(0, 50) + "...";
* html += " <h5> " + mota + "</h5>";
* html += "</td> </tr>";*/
html += " <tr><td> " + BaseView.GetStringFieldValue(row, "ngaydang") + "</td>";
html += "</tr> </table></div>";
html += "<div class='clear'></div></div>";
}
html += "";
lbBaiMoi.Text = html;
}
//包装首检巡检打印标签
public bool PackPrintLabel(string MFPlansId, int PackCheckType, out string returnMsg)
{
try
{
using (SqlConnection conn = DBClass.getSqlConnection())
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandTimeout = 120;
cmd.CommandText = "P_PrintFQCCheckLabel_Pack_Submit";
cmd.Parameters.AddWithValue("@MFPlansEntryId", MFPlansId);
cmd.Parameters.AddWithValue("@PackCheckType", PackCheckType);
cmd.Parameters.AddWithValue("@UserId", "");
cmd.Parameters.AddWithValue("@ResourceId", System.Configuration.ConfigurationManager.AppSettings["DefaultResourceId"]);
cmd.Parameters.Add("@Result_Msg", SqlDbType.NVarChar, 300).Direction = ParameterDirection.Output;
cmd.Parameters.Add("@Return", SqlDbType.NVarChar, 300).Direction = ParameterDirection.Output;
cmd.ExecuteNonQuery();
int ret_Value = Convert.ToInt32(cmd.Parameters["@Return"].Value);
returnMsg = (cmd.Parameters["@Result_Msg"].Value).ToString();
cmd.Parameters.Clear();
conn.Close();
if (ret_Value >= 0)
{
return(true);
}
else
{
return(false);
}
}
}
}
catch (Exception ex)
{
throw;
}
}
public ActionResult MachineDetail(FormCollection form, UploadFile up)
{
int id = Convert.ToInt32(up.FileID);
using (DBClass context = new DBClass())
{
int totalPoint = 0;
context.AddParameter("@FileID", id);
DataTable DEPoint = context.getData("getDEPointCountbyFileID", CommandType.StoredProcedure);
foreach (DataRow dr in DEPoint.Rows)
{
totalPoint += Convert.ToInt32(dr["Point_Count"]);
}
context.AddParameter("@FileID", id);
DataTable NDEPoint = context.getData("getNDEPointCountbyFileID", CommandType.StoredProcedure);
foreach (DataRow dr in NDEPoint.Rows)
{
totalPoint += Convert.ToInt32(dr["Point_Count"]);
}
if (context.ExecuteNonQuery("Update UploadData set PointCount = '" + totalPoint + "' where ID = '" + id + "' ", CommandType.Text) > 0)
{
DataTable dt1 = context.getData("Select AnalysisType from UploadData where ID = '" + id + "'", CommandType.Text);
double cost = 0;
if (Convert.ToString(dt1.Rows[0]["AnalysisType"]) == "Indicative Analysis")
{
cost = 100 * totalPoint;
}
else
{
cost = 200 * totalPoint;
}
context.ExecuteNonQuery("Update PaymentDetail set Amount = '" + cost + "' where FileID = '" + id + "' ", CommandType.Text);
ModelState.Clear();
return(RedirectToAction("ClientList", "Clients"));
}
}
return(View());
}
//保存质检临时数据
public bool SaveTempData(string FQCCheckPackId, string Describe, string XMLData, out string returnMsg)
{
try
{
using (SqlConnection conn = DBClass.getSqlConnection())
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandTimeout = 120;
cmd.CommandText = "P_FQCPackResult_TempSave";
cmd.Parameters.AddWithValue("@FQCCheckPackId", FQCCheckPackId);
cmd.Parameters.AddWithValue("@Describe", Describe);
cmd.Parameters.AddWithValue("@XMLData", XMLData);
cmd.Parameters.Add("@Result_Msg", SqlDbType.NVarChar, 300).Direction = ParameterDirection.Output;
cmd.Parameters.Add("@Return", SqlDbType.NVarChar, 300).Direction = ParameterDirection.Output;
cmd.ExecuteNonQuery();
int ret_Value = Convert.ToInt32(cmd.Parameters["@Return"].Value);
returnMsg = (cmd.Parameters["@Result_Msg"].Value).ToString();
cmd.Parameters.Clear();
conn.Close();
if (ret_Value >= 0)
{
return(true);
}
else
{
return(false);
}
}
}
}
catch (Exception ex)
{
throw;
}
}
public Boolean UpdateUser_types(FormCollection f)
{
String Sqltext = "update User_types set usertypeid =@usertypeid,usertypename =@usertypename where usertypeid=@usertypeid";
SqlConnection con = DBClass.mycon();
SqlCommand cmd = new SqlCommand(Sqltext, con);
cmd.Parameters.AddWithValue("@usertypeid", f["usertypeid"]);
cmd.Parameters.AddWithValue("@usertypename", f["usertypename"]);
int ab = cmd.ExecuteNonQuery();
con.Close();
if (ab > 0)
{
return(true);
}
else
{
return(false);
}
}
public Boolean UpdateDiv_master(FormCollection f)
{
String Sqltext = "update Div_master set divid =@divid,divname =@divname where divid=@divid";
SqlConnection con = DBClass.mycon();
SqlCommand cmd = new SqlCommand(Sqltext, con);
cmd.Parameters.AddWithValue("@divid", f["divid"]);
cmd.Parameters.AddWithValue("@divname", f["divname"]);
int ab = cmd.ExecuteNonQuery();
con.Close();
if (ab > 0)
{
return(true);
}
else
{
return(false);
}
}
protected void Button2_Click(object sender, System.EventArgs e)
{
string strKeywordInput = keyword.Text.ToString().Trim();
string sql = "select * from keyword where operator = 'hy'";
string strKeyword = "";
DBClass db = new DBClass(sql);
SqlDataReader dr = db.returndr();
if (dr.Read())
{
strKeyword = dr["keyword"].ToString().Trim();
}
db.dbClose();
if (strKeywordInput == strKeyword)
{
del.Enabled = true;
}
else
{
Response.Write("<script language = 'javascript'>alert('验证码不正确！');</script>");
}
}
private void txtST_NAME_KeyUp(object sender, KeyEventArgs e)
{
if (Edit == false)
{
var sql = "SELECT [ST_UNIT],[ST_NAME],[HIDE] FROM [COS_UNIT] WHERE [ST_NAME] LIKE '%" + txtST_NAME.Text + "%' ORDER BY ST_NAME ASC";
var dt = new DBClass().SqlGetData(sql);
dgv1.DataSource = dt;
dgv1.Columns[0].HeaderText = "รหัส";
dgv1.Columns[1].HeaderText = "ชื่อหน่วยอะไหล่";
dgv1.Columns[2].HeaderText = "แสดง";
dgv1.Columns[0].Width = 100;
dgv1.Columns[1].Width = 80;
dgv1.Columns[2].Width = 80;
}
if (txtST_NAME.TextLength > 0)
{
button1.Enabled = true;
button2.Enabled = false;
button3.Enabled = true;
}
}
//if login username and password match then return true boolen value
public bool LoginSignin()
{
// bool x = false;
try
{
String sql = " SELECT [user_name] , [password] FROM [BlackLotusDB].[dbo].[LoginTable] WHERE [user_name] = '" + UserName + "' AND [password] = '" + Password + "' ";
string u = "";
string p = "";
// SqlDataReader d = DBClass.dataAdapterMethod(sql);
DataSet d = DBClass.DataSetMethod(sql);
if (d.Tables[0].Rows.Count > 0)
{
//data set obj store vavue as a custom table
u = d.Tables[0].Rows[0][0].ToString();
p = d.Tables[0].Rows[0][1].ToString();
if ((UserName == u) && (Password == p))
{
return(true);
}
else
{
return(false);
}
}
else
{
return(false);
}
}
catch (Exception)
{
throw;
}
}
/// <summary>
/// 获取T_Freight表数据
/// </summary>
public void getFreight(string con_id)
{
string sql = "";
sql = "select * from T_Freight as t1 where carrier_id = '" + con_id + "' ";
DataSet ds = new DataSet();
ds = DBClass.execQuery(sql);
DataTable dt = new DataTable();
dt.Columns.Add(new DataColumn("id", typeof(string)));
dt.Columns.Add(new DataColumn("weight", typeof(string)));
dt.Columns.Add(new DataColumn("formula", typeof(string)));
dt.Columns.Add(new DataColumn("href", typeof(string)));
if (ds.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
DataRow dr = dt.NewRow();
dr["id"] = ds.Tables[0].Rows[i]["id"];
string weight = "";
weight = ds.Tables[0].Rows[i]["min_num"].ToString() +
ds.Tables[0].Rows[i]["char1"].ToString() +
"W" +
ds.Tables[0].Rows[i]["char2"].ToString() +
ds.Tables[0].Rows[i]["max_num"].ToString();
dr["weight"] = weight;
dr["formula"] = ds.Tables[0].Rows[i]["formula"];
dr["href"] = "?";
dt.Rows.Add(dr);
}
}
this.DG2.CanUserAddRows = false;
this.DG2.ItemsSource = dt.DefaultView;
}
private void btnDelete3_Click(object sender, EventArgs e)
{
var dgv = dgv3;
var btnSave = btnSave3;
var btnEdit = btnEdit3;
var btnDelete = btnDelete3;
var btnCancel = btnCancel3;
if (dgv.CurrentRow.Selected == false)
{
return;
}
try
{
if (MessageBox.Show("คุณต้องการลบ" + txtU_NAME.Text + " ใช่หรือไม่?", "ยืนยัน", MessageBoxButtons.YesNo) == DialogResult.Yes)
{
var delete = "delete COS_USER WHERE U_ID='" + dgv.SelectedCells[0].Value + "'";
int idelete = new DBClass().SqlExecute(delete);
MessageBox.Show("ลบข้อมูลเสร็จเรียบร้อยแล้ว", "ผลการทำงาน", MessageBoxButtons.OK, MessageBoxIcon.Information);
ShowData3();
ClearText3();
txtU_NAME.Focus();
btnSave.Enabled = false;
btnEdit.Enabled = false;
btnDelete.Enabled = false;
btnCancel.Enabled = false;
}
else
{
return;
}
}
catch (Exception ex)
{
MessageBox.Show("ลบข้อมูลไม่ได้เนื่องจาก " + ex.Message);
}
}
public int ReportIncident(
string truck,
string driver,
string location,
string incDescrip,
DateTime incidentDate)
{
int insertedRows = -1;
DBClass dataAccess = new DBClass();
try
{
StringBuilder sqlStmnt = new StringBuilder();
sqlStmnt = new StringBuilder();
sqlStmnt.Append("INSERT INTO BTC_Incident (Truck, Driver," +
" IncidentDescription, Location, IncidentDate) VALUES ('");
sqlStmnt.Append(truck);
sqlStmnt.Append("','");
sqlStmnt.Append(driver);
sqlStmnt.Append("','");
sqlStmnt.Append(incDescrip);
sqlStmnt.Append("','");
sqlStmnt.Append(location);
sqlStmnt.Append("','");
sqlStmnt.Append(incidentDate.ToShortDateString());
sqlStmnt.Append("');");
insertedRows = dataAccess.ExecuteSQLStatement(sqlStmnt.ToString());
}
catch (Exception ex)
{
throw ex;
}
return(insertedRows);
}
private void alumno_delete(object sender, RoutedEventArgs e)
{
if (txtEliminarId.Text != "")
{
DBClass.GetConnectionStrings();
try
{
DBClass.openConnection();
string query = "DELETE FROM natacadT WHERE Id='" + this.txtEliminarId.Text + "'";
//string query = "DELETE FROM natacadT WHERE Id='" + this.txtID.Text + "'";
SqlCommand createCom = new SqlCommand(query, DBClass.con);
createCom.ExecuteNonQuery();
MessageBox.Show("Alumno eliminado");
DBClass.closeConnection();
// mostrar
DBClass.openConnection();
//DBClass.sql = "SELECT[UserID], [FirstName], [LastName], [DateOfBirth], [CompanyID], [CreatedDate], [CreatedBy], [ModifiedBy], [ModifiedDate] FROM Table1;";
DBClass.sql = "SELECT [Id], [Nombre], [Apellidos], [Edad], [Grado], [Fecha_de_nacimiento], [Municipio], [Direccion], [Nombre_Tutor], [Apellidos_Tutor], [Celular_Tutor] FROM natacadT;";
DBClass.cmd.CommandType = CommandType.Text;
DBClass.cmd.CommandText = DBClass.sql;
//
DBClass.da = new SqlDataAdapter(DBClass.cmd);
DBClass.dt = new DataTable();
DBClass.da.Fill(DBClass.dt);
myDataGrid.ItemsSource = DBClass.dt.DefaultView;
DBClass.closeConnection();
txtEliminarId.Text = "";
}
catch (Exception ex)
{
MessageBox.Show("error: " + ex.Message);
}
}
else
{
MessageBox.Show("Por favor coloque el ID del alumno");
}
}
private async void ImageTapped(object sender, EventArgs e)
{
MyFirstTable myTable = new MyFirstTable();
myTable.cource = "Xamarin";
myTable.name = "Nitin";
DBClass.insertIntoMyFirstTable(myTable);
var Data = DBClass.getFirstTableData();
DBClass.UpdateName("Nagarjun");
Data = DBClass.getFirstTableData();
DBClass.DeleteRecord("Nagarjun");
Data = DBClass.getFirstTableData();
NavigationPage.SetHasNavigationBar(this, true);
if (!CrossMedia.Current.IsCameraAvailable || !CrossMedia.Current.IsTakePhotoSupported)
{
await DisplayAlert("No Camera", ":( No camera available.", "OK");
return;
}
var file = await CrossMedia.Current.TakePhotoAsync(new Plugin.Media.Abstractions.StoreCameraMediaOptions
{
Directory = "Sample",
Name = "test.jpg"
});
if (file == null)
{
return;
}
await DisplayAlert("File Location", file.Path, "OK");
var stream = file.GetStream();
file.Dispose();
}
public void DataGrid_Loaded()
{
DBClass.openConnection();
DBClass.sql = "select * from therapists";
DBClass.cmd.CommandType = CommandType.Text;
DBClass.cmd.CommandText = DBClass.sql;
DBClass.da = new SqlDataAdapter(DBClass.cmd);
DBClass.dt = new DataTable();
DBClass.da.Fill(DBClass.dt);
// wyciągamy dane
int j = 0;
//Console.WriteLine("Przy bazie" + DBClass.dt);
TherapistsList.Clear();
using (SqlDataReader reader = DBClass.cmd.ExecuteReader())
{
while (reader.Read())
{
for (j = 0; j <= reader.FieldCount - 1; j++) // Looping throw colums
{
data[j] = reader.GetValue(j).ToString();
}
if (data[0] != "0")
{
TherapistsList.Add(new TherapistModel {
IDTherapist = (data[0]), FirstNameTherapist = data[1], LastNameTherapist = data[2], EmailTherapist = data[3], PhoneTherapist = data[4], NoteTherapist = data[5]
});
}
//Console.WriteLine("IDTherapist" + reader["IDTherapist"].ToString());
//Console.WriteLine("FirstNameTherapist" + reader["FirstNameTherapist"].ToString());
}
}
DBClass.closeConnection();
}
public static string LoadAccountCombo(string BranchCode)
{
if (HttpContext.Current.Session["UserName"] == null)
{
return "Error: You are not logged-in.";
}
DBClass db = new DBClass("Accounts");
db.Con.Open();
db.Com.CommandText = String.Format("SELECT [AccountCode],[Description] from account where left(accountcode,5)='{0}' AND left(catagorycode,5) in ('02-06','01-04','02-04','04-01','05-01','05-02','05-03')", BranchCode);
SqlDataAdapter da = new SqlDataAdapter(db.Com);
DataTable dt = new DataTable();
da.Fill(dt);
JavaScriptSerializer serializer = new JavaScriptSerializer();
List<Dictionary<string, object>> rows = new List<Dictionary<string, object>>();
Dictionary<string, object> row;
foreach (DataRow dr in dt.Rows)
{
row = new Dictionary<String, Object>();
foreach (DataColumn col in dt.Columns)
{
row.Add(col.ColumnName, dr[col]);
}
rows.Add(row);
}
db.Con.Close();
db = null;
return serializer.Serialize(rows);
}
public static string LoadCashAccountCombo(string Branch)
{
if (HttpContext.Current.Session["UserName"] == null)
{
return "Error: You are not logged-in.";
}
DBClass db = new DBClass("Accounts");
db.Con.Open();
db.Com.CommandText = "SELECT [AccountCode],[Description] FROM [Account] WHERE left(AccountCode,5)='" + Branch + "' AND right(left(AccountCode,14),8)in ('02-06-03','02-06-04')";
SqlDataAdapter da = new SqlDataAdapter(db.Com);
DataTable dt = new DataTable();
da.Fill(dt);
JavaScriptSerializer serializer = new JavaScriptSerializer();
List<Dictionary<string, object>> rows = new List<Dictionary<string, object>>();
Dictionary<string, object> row;
foreach (DataRow dr in dt.Rows)
{
row = new Dictionary<String, Object>();
foreach (DataColumn col in dt.Columns)
{
row.Add(col.ColumnName, dr[col]);
}
rows.Add(row);
}
db.Con.Close();
db = null;
return serializer.Serialize(rows);
}
public static string LoadCurrencyCombo()
{
if (HttpContext.Current.Session["UserName"] == null)
{
return "Error: You are not logged-in.";
}
DBClass db = new DBClass("Accounts");
db.Con.Open();
db.Com.CommandText = "SELECT [CurCode],[CurDesc] FROM [Currencies] WHERE Status = 'True'";
SqlDataAdapter da = new SqlDataAdapter(db.Com);
DataTable dt = new DataTable();
da.Fill(dt);
JavaScriptSerializer serializer = new JavaScriptSerializer();
List<Dictionary<string, object>> rows = new List<Dictionary<string, object>>();
Dictionary<string, object> row;
foreach (DataRow dr in dt.Rows)
{
row = new Dictionary<String, Object>();
foreach (DataColumn col in dt.Columns)
{
row.Add(col.ColumnName, dr[col]);
}
rows.Add(row);
}
db.Con.Close();
db = null;
return serializer.Serialize(rows);
}
public static string Search(int PageNumber, int PageSize, string sql)
{
if (HttpContext.Current.Session["UserName"] == null)
{
return "Error: You are not logged-in.";
}
DBClass db = new DBClass("Accounts");
db.Con.Open();
if (String.IsNullOrEmpty(sql))
db.Com.CommandText = "SELECT [AccountCode],[Description] FROM [Account]";
else
db.Com.CommandText = sql;
SqlDataAdapter da = new SqlDataAdapter(db.Com);
DataTable dt = new DataTable();
da.Fill(dt);
DataTable paggedtable = new DataTable();
paggedtable = dt.Clone();
int initial = (PageNumber - 1) * PageSize + 1;
int last = initial + PageSize;
for (int i = initial - 1; i < last - 1; i++)
{
if (i >= dt.Rows.Count)
{
break;
}
paggedtable.ImportRow(dt.Rows[i]);
}
System.Web.Script.Serialization.JavaScriptSerializer serializer = new System.Web.Script.Serialization.JavaScriptSerializer();
List<Dictionary<string, object>> rows = new List<Dictionary<string, object>>();
Dictionary<string, object> row;
foreach (DataRow dr in dt.Rows)
{
row = new Dictionary<String, Object>();
foreach (DataColumn col in dt.Columns)
{
row.Add(col.ColumnName, dr[col]);
}
rows.Add(row);
}
db.Con.Close();
db = null;
return String.Format("{{\"total\":{0},\"rows\":{1}}}", dt.Rows.Count, serializer.Serialize(rows));
}
public static string Update(string id, string description, string branchID, string AGglCode, string FOglCode, string SDglCode, string COGSglCOde)
{
if (HttpContext.Current.Session["UserName"] == null)
{
return "Error: You are not logged-in.";
}
DBClass db = new DBClass("SCM");
db.Con.Open();
db.Com.CommandText = String.Format("UPDATE [costsetupOverheadMainheadGlCode] SET [MohID]='{0}', [BranchID]='{1}', [AGglCode]='{2}', [FOglCode]='{3}', [SDglCode]='{4}', [COGSglCode]='{5}' WHERE [MohID]='{0}'", id, branchID, AGglCode, FOglCode, SDglCode, COGSglCOde);
db.Com.ExecuteNonQuery();
db.Con.Close();
db = null;
return "Record updated successfully.";
}
public static string LoadMainHeadCombo()
{
if (HttpContext.Current.Session["UserName"] == null)
{
return "Error: You are not logged-in.";
}
DBClass db = new DBClass("Accounts");
db.Con.Open();
db.Com.CommandText = "SELECT CompCode, Description from branches where CompCode = '"+ HttpContext.Current.Session["CompanyId"] + "'";
SqlDataAdapter da = new SqlDataAdapter(db.Com);
DataTable dt = new DataTable();
da.Fill(dt);
JavaScriptSerializer serializer = new JavaScriptSerializer();
List<Dictionary<string, object>> rows = new List<Dictionary<string, object>>();
Dictionary<string, object> row;
foreach (DataRow dr in dt.Rows)
{
row = new Dictionary<String, Object>();
foreach (DataColumn col in dt.Columns)
{
row.Add(col.ColumnName, dr[col]);
}
rows.Add(row);
}
db.Con.Close();
db = null;
return serializer.Serialize(rows);
}
public static string GetMaxId()
{
if (HttpContext.Current.Session["UserName"] == null)
{
return "Error: You are not logged-in.";
}
DBClass db = new DBClass("SCM");
string Id;
db.Con.Open();
db.Com.CommandText = String.Format("SELECT CAST(isNULL(MAX(CAST([id] AS INT)),0)+1 AS VARCHAR(2)) AS Id FROM [costsetupItemMainhead]");
Id = (string)db.Com.ExecuteScalar();
if (Id.Length == 1)
Id = "0" + Id;
db.Com.ExecuteNonQuery();
db.Con.Close();
db = null;
return Id;
}
public static string GetRow(string RowId)
{
if (HttpContext.Current.Session["UserName"] == null)
{
return "Error: You are not logged-in.";
}
DBClass db = new DBClass("SCM");
db.Con.Open();
db.Com.CommandText = "SELECT [id] ,[description] FROM [costsetupItemMainhead] WHERE [id] = '" + RowId + "'";
SqlDataReader dr = db.Com.ExecuteReader();
JavaScriptSerializer serailizer = new JavaScriptSerializer();
SetupLocationEntry SetupLocation = new SetupLocationEntry();
if (dr.HasRows)
{
while (dr.Read())
{
SetupLocation.BankCode = dr[0].ToString();
SetupLocation.BankDescription = dr[1].ToString();
}
}
if (dr.IsClosed == false)
dr.Close();
db.Con.Close();
db = null;
return serailizer.Serialize(SetupLocation);
}
