private void Filldatatable(string itemnumber)
{
_acountsTb.Clear();
string sql = "SELECT * FROM [SPM_Database].[dbo].[Inventory] WHERE [ItemNumber]='" + itemnumber + "'";
try
{
if (connectapi.cn.State == ConnectionState.Closed)
{
connectapi.cn.Open();
}
SqlDataAdapter _adapter = new SqlDataAdapter(sql, connectapi.cn);
_adapter?.Fill(_acountsTb);
if (_acountsTb.Rows.Count > 0)
{
Fillinfo();
}
else
{
connectapi.Addcpoieditemtosqltablefromgenius(itemnumber, itemnumber);
Fillinfo();
}
}
catch (SqlException ex)
{
MessageBox.Show(ex.Message, "SPM Connect New Item - Fill Data Table", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
connectapi.cn.Close();
}
}
internal KensaIraiTblDataSet.KensaIraiTblKensakuDataTable GetDataBySearchCond(string hokenjoCd,
string kensaIraiNendo,
string kensaIraiSetchishaNm,
string kensaIraiShisetsuNm,
string kensaIraiDtFrom,
string kensaIraiDtTo,
string kensaDtFrom,
string kensaDtTo,
string kensaIraiHoteiKbn)
{
SqlCommand command = CreateSqlCommand(hokenjoCd,
kensaIraiNendo,
kensaIraiSetchishaNm,
kensaIraiShisetsuNm,
kensaIraiDtFrom,
kensaIraiDtTo,
kensaDtFrom,
kensaDtTo,
kensaIraiHoteiKbn);
SqlDataAdapter adpt = new SqlDataAdapter(command);
adpt.SelectCommand.Connection = this.Connection;
KensaIraiTblDataSet.KensaIraiTblKensakuDataTable dataTable = new KensaIraiTblDataSet.KensaIraiTblKensakuDataTable();
adpt.Fill(dataTable);
return dataTable;
}
protected void Fill_User_Header()
{
DataView view = null;
SqlConnection con;
SqlCommand cmd = new SqlCommand();
DataSet ds = new DataSet();
DataTable dt = new DataTable();
System.Configuration.Configuration rootWebConfig = System.Web.Configuration.WebConfigurationManager.OpenWebConfiguration("/BitOp");
System.Configuration.ConnectionStringSettings connString;
connString = rootWebConfig.ConnectionStrings.ConnectionStrings["BopDBConnectionString"];
con = new SqlConnection(connString.ToString());
cmd.Connection = con;
con.Open();
string sql = @"SELECT Fecha_Desde, Inicio_Nombre, Region, Supervisor
FROM Criterios
WHERE Criterio_ID = " + @Criterio_ID;
SqlDataAdapter da = new SqlDataAdapter(sql, con);
da.Fill(ds);
dt = ds.Tables[0];
view = new DataView(dt);
foreach (DataRowView row in view)
{
Lbl_Fecha_Desde.Text = row["Fecha_Desde"].ToString("dd-MM-yyyy");
Lbl_Inicio_Descrip.Text = row["Inicio_Nombre"].ToString();
Lbl_Region.Text = row["Region"].ToString();
Lbl_Supervisor.Text = row["Supervisor"].ToString();
}
con.Close();
}
//use this for SELECT statements
public static DataTable GetData(string SelectStatement)
{
SqlDataAdapter da = new SqlDataAdapter(SelectStatement, ConnectionString);
DataTable dt = new DataTable();
da.Fill(dt);
return dt;
}
internal YoshiZaikoTblDataSet.YoshiZaikoTblKensakuDataTable GetDataBySearchCond(string shishoCd, string yoshiCdFrom, string yoshiCdTo, string yoshiNm)
{
SqlCommand command = CreateSqlCommand(shishoCd, yoshiCdFrom, yoshiCdTo, yoshiNm);
SqlDataAdapter adpt = new SqlDataAdapter(command);
adpt.SelectCommand.Connection = this.Connection;
YoshiZaikoTblDataSet.YoshiZaikoTblKensakuDataTable dataTable = new YoshiZaikoTblDataSet.YoshiZaikoTblKensakuDataTable();
adpt.Fill(dataTable);
return dataTable;
}
internal KenchikuyotoMstDataSet.KenchikuyotoMstKensakuDataTable GetDataBySearchCond(string kenchikuyotoDaibunrui, string kenchikuyotoNm)
{
SqlCommand command = CreateSqlCommand(kenchikuyotoDaibunrui, kenchikuyotoNm);
SqlDataAdapter adpt = new SqlDataAdapter(command);
adpt.SelectCommand.Connection = this.Connection;
KenchikuyotoMstDataSet.KenchikuyotoMstKensakuDataTable dataTable = new KenchikuyotoMstDataSet.KenchikuyotoMstKensakuDataTable();
adpt.Fill(dataTable);
return dataTable;
}
internal YubinNoAdrMstDataSet.YubinNoAdrMstKensakuDataTable GetDataBySearchCond(string zipCd, string address)
{
SqlCommand command = CreateSqlCommand(zipCd, address);
SqlDataAdapter adpt = new SqlDataAdapter(command);
adpt.SelectCommand.Connection = this.Connection;
YubinNoAdrMstDataSet.YubinNoAdrMstKensakuDataTable dataTable = new YubinNoAdrMstDataSet.YubinNoAdrMstKensakuDataTable();
adpt.Fill(dataTable);
return dataTable;
}
private void Consultar()
{
try
{
//Crio a variável que irá armazenar a string de conexão
string strConn = @"";
//Crio a conexão por meio do using, que me garante que após o uso, a conexão será fechada
using (SqlConnection objConn = new SqlConnection(strConn))
{
//Passo a instrução SQL por meio do SqlCommand e concateno
//meu Where com o que o usuário digitar no TextBox
SqlCommand objCmd = new SqlCommand("SELECT CODIGO, NOME FROM REGISTROS WHERE CODIGO = " +
txtConsultar.Text, objConn);
//Instancio o DataTable passando como parâmetro o SqlCommand
SqlDataAdapter objDtAdapter = new SqlDataAdapter(objCmd);
//Instancio o DataSet
DataSet ds = new DataSet();
//Uso o método Fill do DataAdapter, passando como parâmetro o DataSet e minha Tabela
objDtAdapter.Fill(ds, "REGISTROS");
//Uso o método DataSource de meu GridView, que receberá meu DataSet e chamo o DataBind
dgvResultado.DataSource = ds;
dgvResultado.DataBind();
}
}
catch (Exception ex)
{
throw new Exception(ex.Message.ToString());
}
}
public static DataTable GetList(string sql, params SqlParameter[] ps)
{
using (SqlConnection connection = new SqlConnection(connStr))
{
SqlDataAdapter adapter = new SqlDataAdapter(sql, connection);
if (ps.Length > 0)
{
adapter.SelectCommand.Parameters.AddRange(ps);
}
DataTable dataTable = new DataTable();
adapter.Fill(dataTable);
return dataTable;
}
}
internal ChikuMstDataSet.ChikuMstKensakuDataTable GetDataBySearchCond(
String chikuCdFrom,
String chikuCdTo,
String chikuNm)
{
SqlCommand command = CreateSqlCommand(chikuCdFrom, chikuCdTo, chikuNm);
SqlDataAdapter adpt = new SqlDataAdapter(command);
adpt.SelectCommand.Connection = this.Connection;
ChikuMstDataSet.ChikuMstKensakuDataTable dataTable = new ChikuMstDataSet.ChikuMstKensakuDataTable();
adpt.Fill(dataTable);
return dataTable;
}
internal ShishoMstDataSet.ShishoMstDataTable GetDataBySearchCond(
string shishoCdFrom,
string shishoCdTo,
string shishoNm
)
{
SqlCommand command = CreateSqlCommand(shishoCdFrom, shishoCdTo, shishoNm);
SqlDataAdapter adpt = new SqlDataAdapter(command);
adpt.SelectCommand.Connection = this.Connection;
ShishoMstDataSet.ShishoMstDataTable dataTable = new ShishoMstDataSet.ShishoMstDataTable();
adpt.Fill(dataTable);
return dataTable;
}
internal FileKanriTblDataSet.FileKanriTblKensakuDataTable GetDataBySearchCond(
string fileName,
bool torokuDtUse,
DateTime torokuDtFrom,
DateTime torokuDtTo)
{
SqlCommand command = CreateSqlCommand(fileName,torokuDtUse, torokuDtFrom, torokuDtTo);
SqlDataAdapter adpt = new SqlDataAdapter(command);
adpt.SelectCommand.Connection = this.Connection;
FileKanriTblDataSet.FileKanriTblKensakuDataTable dataTable = new FileKanriTblDataSet.FileKanriTblKensakuDataTable();
adpt.Fill(dataTable);
return dataTable;
}
internal NameMstDataSet.NameMstKensakuDataTable GetDataBySearchCond(
String nameKbn,
String nameCdFrom,
String nameCdTo,
String name)
{
SqlCommand command = CreateNameMstKensakuSqlCommand(nameKbn, nameCdFrom, nameCdTo, name);
SqlDataAdapter adpt = new SqlDataAdapter(command);
adpt.SelectCommand.Connection = this.Connection;
NameMstDataSet.NameMstKensakuDataTable dataTable = new NameMstDataSet.NameMstKensakuDataTable();
adpt.Fill(dataTable);
return dataTable;
}
internal MemoMstDataSet.MemoMstKensakuDataTable GetDataBySearchCond(
string memoDaibunruiCd,
string memo,
bool juyo,
bool hutsu,
bool sentakuKa,
bool sentakuHuka)
{
SqlCommand command = CreateSqlCommand(memoDaibunruiCd, memo, juyo, hutsu, sentakuKa, sentakuHuka);
SqlDataAdapter adpt = new SqlDataAdapter(command);
adpt.SelectCommand.Connection = this.Connection;
MemoMstDataSet.MemoMstKensakuDataTable dataTable = new MemoMstDataSet.MemoMstKensakuDataTable();
adpt.Fill(dataTable);
return dataTable;
}
internal KatashikiMstDataSet.KatashikiMstKensakuDataTable GetDataBySearchCond(
String katashikiMakerCdFrom,
String katashikiMakerCdTo,
String gyoshaNm,
String katashikiCdFrom,
String katashikiCdTo,
String katashikiNm)
{
SqlCommand command = CreateSqlCommand(katashikiMakerCdFrom,
katashikiMakerCdTo,
gyoshaNm,
katashikiCdFrom,
katashikiCdTo,
katashikiNm
);
SqlDataAdapter adpt = new SqlDataAdapter(command);
adpt.SelectCommand.Connection = this.Connection;
KatashikiMstDataSet.KatashikiMstKensakuDataTable dataTable = new KatashikiMstDataSet.KatashikiMstKensakuDataTable();
adpt.Fill(dataTable);
return dataTable;
}
protected void Page_Load(object sender, EventArgs e)
{
if (!X.IsAjaxRequest)
{
this.TreePanelMain.Listeners.ItemClick.Handler = "addTab1(App.Pages, record);";
/////////////DB ENABLE/DISABLE USING USER TASKS//////////////////
SqlConnection con; SqlCommand cmd; SqlDataAdapter myAdapter; DataSet ds;
con = new SqlConnection("server=local;uid=sa;pwd=abc@123;Database=TNT;Connect Timeout=10000;");
try
{
con.Open();
cmd = new SqlCommand("select * from VuAllUsersTasks Where UserID=2 order by TaskGroup", con);
cmd.CommandType = CommandType.Text;
myAdapter = new SqlDataAdapter();
myAdapter.SelectCommand = cmd;
cmd.CommandTimeout = 1000;
ds = new DataSet();
myAdapter.Fill(ds);
if (ds.Tables.Count != 0)
{
dTuserTasks = ds.Tables[0];
}
else
{
dTuserTasks = null;
}
}
catch (Exception EX)
{
throw EX;
}
/////////////DB ENABLE/DISABLE USING USER TASKS//////////////////
//add userName at menubar
//if (dTuserTasks != null && dTuserTasks.Rows.Count > 0)
//{
// lblUserName.Text = dTuserTasks.Rows[0]["UserName"].ToString();
//}
//////////////////Add Nods at runtime
for (int i = 0; i < dTuserTasks.Rows.Count; i++)
{
creatNodeRuntime(dTuserTasks.Rows[i]["TaskName"].ToString(),
dTuserTasks.Rows[i]["TaskKey"].ToString(), dTuserTasks.Rows[i]["TaskGroup"].ToString(),
dTuserTasks.Rows[i]["MenuIcon"].ToString(), dTuserTasks.Rows[i]["PageURL"].ToString()
);
}
//remove group nodes, those who dont have leaf nodes
foreach (string str in NodeIdsOfGroups)
{
//dont delete Parents nodes those who hava sub parents in them.
if (dTuserTasks.Select("TaskGroup = '" + str + "'").Length < 1 && !NodeIdsDontRemove.Contains(str))
{
//TreePanelMain.GetNodeById(str).Set("hidden", true);
TreePanelMain.GetNodeById(str).Remove();
//also remove parents or leafs, influence for front end.
var list = new List <string>(NodeIDsFinance);
list.Remove(str);
NodeIDsFinance = list.ToArray();
list = new List <string>(NodeIDsMis);
list.Remove(str);
NodeIDsMis = list.ToArray();
list = new List <string>(NodeIDsSecurity);
list.Remove(str);
NodeIDsSecurity = list.ToArray();
list = new List <string>(NodesToCollapse);
list.Remove(str);
NodesToCollapse = list.ToArray();
}
}
//////////////////Add Nods at runtime
//////allowd tasks str array object for javascript
string arrayString = string.Empty;
foreach (DataRow row in dTuserTasks.Rows)
{
arrayString += "new String('" + row["TaskKey"].ToString() + "'),";
}
// arrayString = "new String('word1'),new String('word2'), ...etc";
ClientScript.RegisterArrayDeclaration("AllowedTasksKeys", arrayString);
//////allowd tasks str array object for javascript
//////hideForMis str array object for javascript
arrayString = string.Empty;
foreach (string str in NodeIDsMis)
{
arrayString += "new String('" + str + "'),";
}
ClientScript.RegisterArrayDeclaration("NodeIDsMis", arrayString);
//////hideForMis str array object for javascript
//////hideForSec str array object for javascript
arrayString = string.Empty;
foreach (string str in NodeIDsSecurity)
{
arrayString += "new String('" + str + "'),";
}
ClientScript.RegisterArrayDeclaration("NodeIDsSecurity", arrayString);
//////hideForSec str array object for javascript
//////hideForFinance str array object for javascript
arrayString = string.Empty;
foreach (string str in NodeIDsFinance)
{
arrayString += "new String('" + str + "'),";
}
ClientScript.RegisterArrayDeclaration("NodeIDsFinance", arrayString);
//////hideForFinance str array object for javascript
//////// nodesToColapse str array object for javascript
arrayString = string.Empty;
foreach (string str in NodesToCollapse)
{
arrayString += "new String('" + str + "'),";
}
ClientScript.RegisterArrayDeclaration("NodesToCollapse", arrayString);
//////// nodesToColapse str array object for javascript
/////EXPEND THEN COLLAPSE TO MAKE THEM HIDE
for (int i = 0; i < NodesToCollapse.Length; i++)
{
TreePanelMain.GetNodeById(NodesToCollapse[i]).Expand(true);
TreePanelMain.GetNodeById(NodesToCollapse[i]).Collapse(true);
}
/////EXPEND THEN COLLAPSE TO MAKE THEM HIDE
///////////////////////////////NOW HANDELING AT CLIENT SIDE///// SEARCH FOR <AfterRender>///////////////////////////////
/////set Active Menu Item until right frontend event is not found
//MIFinance.AddCls("setActiveTab");
//MISecurity.RemoveCls("setActiveTab");
//MIMIS.RemoveCls("setActiveTab");
/////set Active Menu Item until right frontend event is not found
//////////handeling at backend until find apropirate client load event
//MISNodesHide(true);
//SecurityNodesHide(true);
//FinanceNodesHide(false);
//////////handeling at backend until find apropirate client load event
///////////////////////////////NOW HANDELING AT CLIENT SIDE///// SEARCH FOR <AfterRender>///////////////////////////////
this.TreePanelMain.Title = "Welcome Finance";
//TreePanelMain.GetNodeById("OpeningBalances").Remove();
//TreePanelMain.GetNodeById("ChartofAccounts").Remove();
////////////test
btnid.Text = dTuserTasks.Rows[0]["UserName"].ToString();
////////////test
}
}
public (CharacterAbilityListWithFilterCount, Character, RuleSet) SP_CharacterAbility_GetByCharacterId_Old(int characterId, int rulesetId, int page, int pageSize, int sortType = 1)
{
CharacterAbilityListWithFilterCount result = new CharacterAbilityListWithFilterCount();
List <CharacterAbility> _CharacterAbilityList = new List <CharacterAbility>();
RuleSet ruleset = new RuleSet();
Character character = new Character();
int FilterAplhabetCount = 0;
int FilterEnabledCount = 0;
int FilterLevelCount = 0;
result.AbilityList = _CharacterAbilityList;
result.FilterAplhabetCount = FilterAplhabetCount;
result.FilterEnabledCount = FilterEnabledCount;
result.FilterLevelCount = FilterLevelCount;
short num = 0;
string connectionString = _configuration.GetSection("ConnectionStrings").GetSection("DefaultConnection").Value;
//string qry = "EXEC CharacterAbility_GetByCharacterId @CharacterId='" + characterId + "',@RulesetID='" + rulesetId + "',@page='" + page + "',@size='" + pageSize + "'";
SqlConnection connection = new SqlConnection(connectionString);
SqlCommand command = new SqlCommand();
SqlDataAdapter adapter = new SqlDataAdapter();
DataSet ds = new DataSet();
try
{
connection.Open();
command = new SqlCommand("CharacterAbility_GetByCharacterId", connection);
// Add the parameters for the SelectCommand.
command.Parameters.AddWithValue("@CharacterId", characterId);
command.Parameters.AddWithValue("@RulesetID", rulesetId);
command.Parameters.AddWithValue("@page", page);
command.Parameters.AddWithValue("@size", pageSize);
command.Parameters.AddWithValue("@SortType", sortType);
command.CommandType = CommandType.StoredProcedure;
adapter.SelectCommand = command;
adapter.Fill(ds);
command.Dispose();
connection.Close();
}
catch (Exception ex)
{
command.Dispose();
connection.Close();
}
if (ds.Tables[1].Rows.Count > 0)
{
ruleset = _repo.GetRuleset(ds.Tables[1], num);
}
if (ds.Tables[2].Rows.Count > 0)
{
character = _repo.GetCharacter(ds.Tables[2]);
}
if (ds.Tables[0].Rows.Count > 0)
{
foreach (DataRow row in ds.Tables[0].Rows)
{
CharacterAbility _characterAbility = new CharacterAbility();
_characterAbility.CharacterAbilityId = row["CharacterAbilityId"] == DBNull.Value ? 0 : Convert.ToInt32(row["CharacterAbilityId"].ToString());
_characterAbility.CharacterId = characterId;
_characterAbility.AbilityId = row["AbilityId"] == DBNull.Value ? 0 : Convert.ToInt32(row["AbilityId"].ToString());
_characterAbility.CurrentNumberOfUses = row["CharacterCurrentNumberOfUses"] == DBNull.Value ? 0 : Convert.ToInt32(row["CharacterCurrentNumberOfUses"].ToString());
_characterAbility.MaxNumberOfUses = row["CharacterMaxNumberOfUses"] == DBNull.Value ? 0 : Convert.ToInt32(row["CharacterMaxNumberOfUses"].ToString());
_characterAbility.IsEnabled = row["CharacterIsEnabled"] == DBNull.Value ? false : Convert.ToBoolean(row["CharacterIsEnabled"]);
Ability _ability = new Ability();
_ability.Name = row["Name"] == DBNull.Value ? null : row["Name"].ToString();
_ability.Stats = row["Stats"] == DBNull.Value ? null : row["Stats"].ToString();
_ability.Metatags = row["Metatags"] == DBNull.Value ? null : row["Metatags"].ToString();
_ability.Command = row["Command"] == DBNull.Value ? null : row["Command"].ToString();
_ability.CommandName = row["CommandName"] == DBNull.Value ? null : row["CommandName"].ToString();
_ability.Description = row["Description"] == DBNull.Value ? null : row["Description"].ToString();
_ability.gmOnly = row["gmOnly"] == DBNull.Value ? null : row["gmOnly"].ToString();
_ability.ImageUrl = row["ImageUrl"] == DBNull.Value ? null : row["ImageUrl"].ToString();
_ability.Level = row["Level"] == DBNull.Value ? null : row["Level"].ToString();
_ability.IsDeleted = row["IsDeleted"] == DBNull.Value ? false : Convert.ToBoolean(row["IsDeleted"]);
_ability.IsEnabled = row["IsEnabled"] == DBNull.Value ? false : Convert.ToBoolean(row["IsEnabled"]);
_ability.AbilityId = row["AbilityId"] == DBNull.Value ? 0 : Convert.ToInt32(row["AbilityId"].ToString());
_ability.ParentAbilityId = row["ParentAbilityId"] == DBNull.Value ? 0 : Convert.ToInt32(row["ParentAbilityId"].ToString());
_ability.RuleSetId = row["RuleSetId"] == DBNull.Value ? 0 : Convert.ToInt32(row["RuleSetId"].ToString());
_ability.CurrentNumberOfUses = row["CurrentNumberOfUses"] == DBNull.Value ? 0 : Convert.ToInt32(row["CurrentNumberOfUses"].ToString());
_ability.MaxNumberOfUses = row["MaxNumberOfUses"] == DBNull.Value ? 0 : Convert.ToInt32(row["MaxNumberOfUses"].ToString());
_ability.RuleSet = ruleset;
_characterAbility.Ability = _ability;
//_characterAbility.Character = character;
_CharacterAbilityList.Add(_characterAbility);
}
}
if (ds.Tables[3].Rows.Count > 0)
{
FilterAplhabetCount = ds.Tables[3].Rows[0][0] == DBNull.Value ? 0 : Convert.ToInt32(ds.Tables[3].Rows[0][0]);
FilterLevelCount = ds.Tables[3].Rows[0][1] == DBNull.Value ? 0 : Convert.ToInt32(ds.Tables[3].Rows[0][1]);
}
if (ds.Tables[4].Rows.Count > 0)
{
FilterEnabledCount = ds.Tables[4].Rows[0][0] == DBNull.Value ? 0 : Convert.ToInt32(ds.Tables[4].Rows[0][0]);
}
if (ds.Tables[5].Rows.Count > 0)
{
FilterLevelCount = ds.Tables[5].Rows[0][0] == DBNull.Value ? 0 : Convert.ToInt32(ds.Tables[5].Rows[0][0]);
}
result.AbilityList = _CharacterAbilityList;
result.FilterAplhabetCount = FilterAplhabetCount;
result.FilterEnabledCount = FilterEnabledCount;
result.FilterLevelCount = FilterLevelCount;
//return result;
return(result, character, ruleset);
}
public string drawMenu(string UTID)
{
SqlConnection Conn = new SqlConnection(ConfigurationManager.ConnectionStrings["Con"].ConnectionString);
//PageURL = Request.Url.AbsolutePath;
PageURL = HttpContext.Current.Request.Url.AbsolutePath;
int i = 0;
int j = 0; string InnerHTML = "";
PageURL = PageURL.ToLowerInvariant();
string str = "select distinct FormID As id, FormTitle As name , PID As parentid , Path As path from VW_MENU where PID=0 and UTID='" + UTID + "' order by FormID";
SqlDataAdapter sqladp = new SqlDataAdapter(str, Conn);
DataTable dt = new DataTable();
sqladp.Fill(dt);
InnerHTML = InnerHTML + "<ul>";
for (i = 0; i < dt.Rows.Count; i++)
{
string hasChild = getchildes(Convert.ToInt32(dt.Rows[i]["id"].ToString()), UTID);
if (hasChild == "")
{
string ActiveChild = "";
string path = dt.Rows[i]["path"].ToString();
path = path.ToLowerInvariant();
if (PageURL.IndexOf(path) != -1)
{
ActiveChild = " class=active";
}
InnerHTML = InnerHTML + "<li>";
InnerHTML = InnerHTML + "<a href='" + "../" + dt.Rows[i]["path"].ToString() + "" + "'>";
InnerHTML = InnerHTML + "<span class=menu-icon'><i class='fa fa-envelope'></i></span> ";
InnerHTML = InnerHTML + "<span class='menu-text'>" + dt.Rows[i]["name"].ToString() + "</span></a>";
//<span class="menu-badge"><span class="badge vd_bg-red">78</span></span>
InnerHTML = InnerHTML + "</li>";
}
else
{
string ActiveParent = "";
if (hasChild.IndexOf("class=active") != -1)
{
ActiveParent = " active";
}
string TotalChild = hasChild;
TotalChild = TotalChild.Substring(0, TotalChild.IndexOf("`"));
hasChild = hasChild.Substring(hasChild.IndexOf("`") + 1);
InnerHTML = InnerHTML + "<li>";
InnerHTML = InnerHTML + "<a href='javascript:void(0);' data-action='click-trigger'>";
if (i == 0)
{
InnerHTML = InnerHTML + "<span class='menu-icon'><i class='fa fa-dashboard'></i></span> ";
}
if (i == 1)
{
InnerHTML = InnerHTML + "<span class='menu-icon'><i class='fa fa-envelope'></i></span> ";
}
if (i == 2)
{
InnerHTML = InnerHTML + "<span class='menu-icon'><i class='icon-palette'></i></span> ";
}
if (i == 3)
{
InnerHTML = InnerHTML + "<span class='menu-icon'><i class='icon-bookmark'></i></span> ";
}
if (i == 4)
{
InnerHTML = InnerHTML + "<span class='menu-icon'><i class='fa fa-sitemap'></i></span> ";
}
if (i == 5)
{
InnerHTML = InnerHTML + "<span class='menu-icon'><i class='fa fa-code'></i></span> ";
}
if (i == 6)
{
InnerHTML = InnerHTML + "<span class='menu-icon'><i class='fa fa-th-list'></i></span> ";
}
if (i == 7)
{
InnerHTML = InnerHTML + "<span class='menu-icon'><i class='icon-tools'></i></span> ";
}
if (i == 8)
{
InnerHTML = InnerHTML + "<span class='menu-icon'><i class='fa fa-table'></i></span> ";
}
if (i == 9)
{
InnerHTML = InnerHTML + "<span class='menu-icon'><i class='fa fa-signal'></i></span> ";
}
if (i == 10)
{
InnerHTML = InnerHTML + "<span class='menu-icon'><i class='fa fa-map-marker'></i></span> ";
}
if (i == 11)
{
InnerHTML = InnerHTML + "<span class='menu-icon'><i class='icon-newspaper'></i></span> ";
}
if (i == 12)
{
InnerHTML = InnerHTML + "<span class='menu-icon'><i class='fa fa-desktop'></i></span> ";
}
if (i == 13)
{
InnerHTML = InnerHTML + "<span class='menu-icon'><i class='fa fa-shopping-cart'></i></span> ";
}
if (i == 14)
{
InnerHTML = InnerHTML + "<span class='menu-icon'><i class='fa fa-dashboard'></i></span> ";
}
if (i == 15)
{
InnerHTML = InnerHTML + "<span class='menu-icon'><i class='fa fa-dashboard'></i></span> ";
}
if (i == 16)
{
InnerHTML = InnerHTML + "<span class='menu-icon'><i class='fa fa-dashboard'></i></span> ";
}
if (i == 17)
{
InnerHTML = InnerHTML + "<span class='menu-icon'><i class='fa fa-dashboard'></i></span> ";
}
if (i == 18)
{
InnerHTML = InnerHTML + "<span class='menu-icon'><i class='fa fa-dashboard'></i></span> ";
}
if (i == 19)
{
InnerHTML = InnerHTML + "<span class='menu-icon'><i class='fa fa-dashboard'></i></span> ";
}
InnerHTML = InnerHTML + "<span class='menu-text'>" + dt.Rows[i]["name"].ToString() + "</span> ";
InnerHTML = InnerHTML + "<span class='menu-badge'><span class='badge vd_bg-black-30'><i class='fa fa-angle-down' style='color: red;font-size: 17px;'></i></span></span>";
InnerHTML = InnerHTML + "</a>";
InnerHTML = InnerHTML + "<div class='child-menu' data-action='click-target'><ul>";
InnerHTML = InnerHTML + hasChild;
InnerHTML = InnerHTML + "</ul></div>";
InnerHTML = InnerHTML + "</li> ";
}
}
InnerHTML = InnerHTML + "</ul>";
//InnerHTML = InnerHTML + "<li><a href='javascript:;' class='sidebar-minify-btn' data-click='sidebar-minify'><i class='fa fa-angle-double-left'></i></a></li>";
//InnerHTML = InnerHTML + "</ul>";
//dvMenu.InnerHtml = InnerHTML;
return(InnerHTML);
}
/*
* --> BOTON ELIMINAR ENCARGADOS TIENDAS SPLT
*/
private void RegistrarEncargadoTienda_Click(object sender, EventArgs e)
{
if (txtIDEmpleadoSPLT.Text.Length == 0 || txtNombreEmpleadoSPLT.Text.Length == 0 || txtApellidoEmpleadoSPLT.Text.Length == 0 || txtidencargadotienda.Text.Length == 0 || txtcodigoUtiendas.Text.Length == 0 || txtdireccionCtiendas.Text.Length == 0 || txttelefonotiendas.Text.Length == 0)
{
// CREANDO MENSAJE EN VENTANA FLOTANTE PERSONALIZADO
if (MessageBox.Show("Lo sentimos pero ha ocurrido un error, tome en cuenta lo siguiente:\n- No puede dejar campos vacíos.\n- Por favor sea cuidadoso a la hora de eliminar un encargado de tienda, respetando las métricas establecidas por la empresa.", "Error al Intentar Eliminar Registro",
MessageBoxButtons.OK, MessageBoxIcon.Error) == DialogResult.OK)
{
;
}
}
else
{
// CREANDO VARIABLE TIPO QUERY QUE ALMACENA LA CADENA DE ELIMINAR X REGISTRO CON SU CONDICION
string query = "DELETE FROM EncargadosTiendas WHERE ID_encargado=@ID_encargado";
// --> WHERE [ID_encargado]=@[ID_encargado]" IMPORTANTE: SI NO SE REFERENCIA POR DEFECTO NO MUESTRA ERROR PERO SE PRODUCE UN ERROR LOGICO, AL ELIMINAR ABSOLUTAMENTE
// TODOS LOS DATOS DE LA TABLA REFERENCIADA EN LA BASE DE DATOS.
con.Open(); // APERTURANDO CONEXION
SqlCommand comando = new SqlCommand(query, con); // CREANDO COMPONENTE DE COMUNICACION HACIA LA BASE DE DATOS
// ENVIO DE PARAMETROS CON LOS DATOS SELECCIONADOS PREVIAMENTE POR EL USUARIO HACIA LA BASE DE DATOS
comando.Parameters.AddWithValue("@ID_encargado", txtidencargadotienda.Text); // REFERENCIA DEL ID UNICO DE CADA ENCARGADO DE TIENDA
comando.ExecuteNonQuery(); // ENVIANDO COMPONENTE QUERY HACIA LA BASE DE DATOS CON NUEVO REGISTRO ACTUALIZADO
// CREANDO MENSAJE EN VENTANA FLOTANTE PERSONALIZADO
// VALIDANDO QUE NO EXISTAN CAMPOS VACIOS Y QUE AL MENOS EL USUARIO SELECCIONE UN REGISTRO A ELIMINAR
if (txtIDEmpleadoSPLT.Text.Length == 0 || txtNombreEmpleadoSPLT.Text.Length == 0 || txtApellidoEmpleadoSPLT.Text.Length == 0 || txtidencargadotienda.Text.Length == 0 || txtcodigoUtiendas.Text.Length == 0 || txtdireccionCtiendas.Text.Length == 0 || txttelefonotiendas.Text.Length == 0)
{
// CREANDO MENSAJE EN VENTANA FLOTANTE PERSONALIZADO
if (MessageBox.Show("Lo sentimos pero ha ocurrido un error, tome en cuenta lo siguiente:\n- No puede dejar campos vacíos.\n- Por favor sea cuidadoso a la hora de eliminar una tienda, respetando las métricas establecidas por la empresa.", "Error al Intentar Eliminar Registro",
MessageBoxButtons.OK, MessageBoxIcon.Error) == DialogResult.OK)
{
;
}
con.Close();// AL FINALIZAR CADA NUEVO REGISTRO, SE PROCEDE A CERRAR LA CONEXION A ESPERA DE UNA NUEVA PETICION
}
else
{
// CREANDO MENSAJE EN VENTANA FLOTANTE PERSONALIZADO
if (MessageBox.Show("Encargado de Tienda Eliminado Con Exito", "Eliminando Encargado De Tienda Existente",
MessageBoxButtons.OK, MessageBoxIcon.Information) == DialogResult.OK)
{
;
}
/*------------------------------------------------------------------------------------------
* -> INICIALIZANDO TEXTBOX A SUS VALORES PREDETERMINADOS ANTES DE CERRAR CONEXION UNA VEZ
* REALIZADO EL NUEVO REGISTRO A LA BASE DE DATOS {CONSTRUCTORES}
* -----------------------------------------------------------------------------------------*/
txtApellidoEmpleadoSPLT.Text = " "; txtNombreEmpleadoSPLT.Text = " "; txtIDEmpleadoSPLT.Text = " ";
txtidencargadotienda.Text = " "; txtcodigoUtiendas.Text = " "; txtdireccionCtiendas.Text = " "; txttelefonotiendas.Text = " ";
txtIDEmpleadoSPLT.Text = " "; txtNombreEmpleadoSPLT.Text = " "; txtApellidoEmpleadoSPLT.Text = " ";
/*---------------------------------------------------------------------------------------------
* AL MOMENTO QUE EL USUARIO INGRESA LOS DATOS, ESTE VUELVE A EJECUTAR EL PROCEDIMIENTO PARA *
* REFRESCAR LA TABLA CONTENEDORA CON LOS N USUARIOS REGISTRADOS EN EL SISTEMA... *
* --------------------------------------------------------------------------------------------
*/
// REFERENCIANDO EL NOMBRE DE LA TABLA A MOSTRAR LOS N DATOS CONTENIDOS EN ELLA
SqlCommand cmd = new SqlCommand("Select ID_encargado, Id_empleado, Nombre_empleado, Apellido_empleado, Codigo_tienda, Direccion_tienda, Tefefono_tienda from EncargadosTiendas", con);
// CREANDO ADAPTADOR DE COMUNICACION HACIA LA BASE DE DATOS PARA EL LLENADO DE REGISTROS...
SqlDataAdapter MostrarRegistros = new SqlDataAdapter();
MostrarRegistros.SelectCommand = cmd;
// REFERENCIANDO LA INVOCACION DE UN NUEVO OBJETO PARA MANEJO DE DATAGRIDVIEW
DataTable TablaRegistros = new DataTable();
// LLENANDO CON TODOS LOS REGISTROS CONTENIDOS EN LA TABLA DE EMPLEADOS
MostrarRegistros.Fill(TablaRegistros);
// INVOCANDO LA REFERENCIA QUE SEA IGUAL AL ADAPTADOR DE LLENADO DE DATOS,
// ES DECIR, TODOS LOS REGISTROS CONTENIDOS DENTRO DE LA TABLA {Empleados}
DetallesEncargadosTiendasSpeedLimit.DataSource = TablaRegistros;
con.Close();// AL FINALIZAR CADA NUEVO REGISTRO, SE PROCEDE A CERRAR LA CONEXION A ESPERA DE UNA NUEVA PETICION
}
}
}
public List <PharmaBusinessObjects.Transaction.PurchaseSaleBookLineItem> GetPurchaseSaleBookLineItemForModify(long purchaseSaleBookHeaderID)
{
List <PharmaBusinessObjects.Transaction.PurchaseSaleBookLineItem> lineitems = new List <PharmaBusinessObjects.Transaction.PurchaseSaleBookLineItem>();
string ConnString = ConfigurationManager.ConnectionStrings["PharmaDBConn"].ConnectionString;
using (SqlConnection connection = new SqlConnection(ConnString))
{
SqlCommand cmd = new SqlCommand("GetPurchaseSaleBookLineItemsForModify", connection);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter()
{
ParameterName = "@PurchaseSaleBookHeaderID", Value = purchaseSaleBookHeaderID
});
SqlDataAdapter sda = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
sda.Fill(dt);
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow row in dt.Rows)
{
PharmaBusinessObjects.Transaction.PurchaseSaleBookLineItem obj = new PharmaBusinessObjects.Transaction.PurchaseSaleBookLineItem()
{
PurchaseSaleBookHeaderID = Convert.ToInt64(row["PurchaseSaleBookHeaderID"]),
PurchaseSaleBookLineItemID = Convert.ToInt64(row["PurchaseSaleBookLineItemID"]),
FifoID = Convert.ToInt64(row["FifoID"] == DBNull.Value ? 0 : row["FifoID"]),
ItemCode = Convert.ToString(row["ItemCode"]),
ItemName = Convert.ToString(row["ItemName"]),
Batch = Convert.ToString(row["Batch"]),
Quantity = Convert.ToDecimal(row["Quantity"] == DBNull.Value ? 0 : row["Quantity"]),
FreeQuantity = Convert.ToDecimal(row["FreeQuantity"] == DBNull.Value ? 0 : row["FreeQuantity"]),
PurchaseSaleRate = Convert.ToDecimal(row["PurchaseSaleRate"] == DBNull.Value ? 0 : row["PurchaseSaleRate"]),
OldPurchaseSaleRate = Convert.ToDecimal(row["PurchaseSaleRate"] == DBNull.Value ? 0 : row["PurchaseSaleRate"]),
EffecivePurchaseSaleRate = Convert.ToDecimal(row["EffecivePurchaseSaleRate"] == DBNull.Value ? 0 : row["EffecivePurchaseSaleRate"]),
PurchaseSaleTypeCode = Convert.ToString(row["PurchaseSaleTypeCode"]),
PurchaseSaleTax = Convert.ToDecimal(row["PurchaseSaleTax"] == DBNull.Value ? 0 : row["PurchaseSaleTax"]),
SurCharge = Convert.ToDecimal(row["SurCharge"] == DBNull.Value ? 0 : row["SurCharge"]),
LocalCentral = Convert.ToString(row["LocalCentral"]),
SGST = Convert.ToDecimal(row["SGST"] == DBNull.Value ? 0 : row["SGST"]),
IGST = Convert.ToDecimal(row["IGST"] == DBNull.Value ? 0 : row["IGST"]),
CGST = Convert.ToDecimal(row["CGST"] == DBNull.Value ? 0 : row["CGST"]),
Amount = Convert.ToDecimal(row["Amount"] == DBNull.Value ? 0 : row["Amount"]),
Discount = Convert.ToDecimal(row["Discount"] == DBNull.Value ? 0 : row["Discount"]),
SpecialDiscount = Convert.ToDecimal(row["SpecialDiscount"] == DBNull.Value ? 0 : row["SpecialDiscount"]),
DiscountQuantity = Convert.ToDecimal(row["DiscountQuantity"] == DBNull.Value ? 0 : row["DiscountQuantity"]),
VolumeDiscount = Convert.ToDecimal(row["VolumeDiscount"] == DBNull.Value ? 0 : row["VolumeDiscount"]),
Scheme1 = Convert.ToDecimal(row["Scheme1"] == DBNull.Value ? 0 : row["Scheme1"]),
Scheme2 = Convert.ToDecimal(row["Scheme2"] == DBNull.Value ? 0 : row["Scheme2"]),
IsHalfScheme = Convert.ToBoolean(row["IsHalfScheme"] == DBNull.Value ? false : row["IsHalfScheme"]),
HalfSchemeRate = Convert.ToDecimal(row["HalfSchemeRate"] == DBNull.Value ? 0 : row["HalfSchemeRate"]),
CostAmount = Convert.ToDecimal(row["CostAmount"] == DBNull.Value ? 0 : row["CostAmount"]),
GrossAmount = Convert.ToDecimal(row["GrossAmount"] == DBNull.Value ? 0 : row["GrossAmount"]),
SchemeAmount = Convert.ToDecimal(row["SchemeAmount"] == DBNull.Value ? 0 : row["SchemeAmount"]),
DiscountAmount = Convert.ToDecimal(row["DiscountAmount"] == DBNull.Value ? 0 : row["DiscountAmount"]),
SurchargeAmount = Convert.ToDecimal(row["SurchargeAmount"] == DBNull.Value ? 0 : row["SurchargeAmount"]),
ConversionRate = Convert.ToDecimal(row["ConversionRate"] == DBNull.Value ? 0 : row["ConversionRate"]),
MRP = Convert.ToDecimal(row["MRP"] == DBNull.Value ? 0 : row["MRP"]),
ExpiryDate = Convert.ToDateTime(row["ExpiryDate"]),
SaleRate = Convert.ToDecimal(row["SaleRate"] == DBNull.Value ? 0 : row["SaleRate"]),
WholeSaleRate = Convert.ToDecimal(row["WholeSaleRate"] == DBNull.Value ? 0 : row["WholeSaleRate"]),
SpecialRate = Convert.ToDecimal(row["SpecialRate"] == DBNull.Value ? 0 : row["SpecialRate"]),
TaxAmount = Convert.ToDecimal(row["TaxAmount"] == DBNull.Value ? 0 : row["TaxAmount"]),
SpecialDiscountAmount = Convert.ToDecimal(row["SpecialDiscountAmount"] == DBNull.Value ? 0 : row["SpecialDiscountAmount"]),
VolumeDiscountAmount = Convert.ToDecimal(row["VolumeDiscountAmount"] == DBNull.Value ? 0 : row["VolumeDiscountAmount"]),
TotalDiscountAmount = Convert.ToDecimal(row["TotalDiscountAmount"] == DBNull.Value ? 0 : row["TotalDiscountAmount"]),
OldPurchaseSaleBookLineItemID = Convert.ToInt64(row["OldPurchaseSaleBookLineItemID"] == DBNull.Value ? 0 : row["OldPurchaseSaleBookLineItemID"]),
BalanceQuantity = Convert.ToDecimal(row["BalanceQuantity"] == DBNull.Value ? 0 : row["BalanceQuantity"]),
UsedQuantity = Convert.ToDecimal(row["UsedQuantity"] == DBNull.Value ? 0 : row["UsedQuantity"]),
PurchaseBillDate = row["PurchaseBillDate"] != DBNull.Value ? Convert.ToDateTime(row["PurchaseBillDate"]) : (DateTime?)null,
PurchaseSrlNo = row["PurchaseSrlNo"] != null?Convert.ToInt32(row["PurchaseSrlNo"]) : (int?)null,
PurchaseVoucherNumber = row["PurchaseVoucherNumber"] != null?Convert.ToString(row["PurchaseVoucherNumber"]) : null,
MfgDate = row["MfgDate"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(row["MfgDate"])
};
lineitems.Add(obj);
}
}
}
return(lineitems);
}
//获取相应桌台类别的桌台记录
public static DataTable LoadTableList(string TableTypeName)
{
System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection();
conn.ConnectionString = ConnStr;
try
{
SqlCommand selectCMD = new SqlCommand();
selectCMD.Connection = conn;
selectCMD.CommandText = "SELECT tableno,tablename,status,begintime FROM TableStatus";
if (TableTypeName != "全部")
{
selectCMD.CommandText = selectCMD.CommandText + " where TabletypeCode='" + GetTableTypeCode(TableTypeName) + "'";
}
selectCMD.CommandTimeout = 30;
SqlDataAdapter dbDA = new SqlDataAdapter();
dbDA.SelectCommand = selectCMD;
conn.Open();
DataSet dbDS = new DataSet();
dbDA.Fill(dbDS, "TableListView");
return dbDS.Tables[0];
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
conn.Close();
}
return null;
}
//检测桌台使用状态
//0=空闲，1=使用，2=预定中，-1=不存在该桌台 与桌台列表单击事件的返回值不同！
public static int GetTableStatus(string tableno)
{
System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection();
conn.ConnectionString = ConnStr;
try
{
SqlCommand selectCMD = new SqlCommand("SELECT status FROM Tablestatus where TableNo='" + tableno + "'", conn);
selectCMD.CommandTimeout = 30;
SqlDataAdapter dbDA = new SqlDataAdapter();
dbDA.SelectCommand = selectCMD;
conn.Open();
DataSet dbDS = new DataSet();
dbDA.Fill(dbDS, "t");
if (! (dbDS.Tables[0].Rows.Count > 0)) //没有该桌台
{
return -1;
}
else
{
//0=空闲，1=使用，2=预定中
return Convert.ToInt32(dbDS.Tables[0].Rows[0]["status"]);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
conn.Close();
}
return 0;
}
public CancelLoadingkResponse CancelLoadingProcess(CancelLoading CL)
{
CancelLoadingkResponse SM = new CancelLoadingkResponse();
try
{
if (CL.UserId == "" || CL.UserId == null)
{
SM.Status = "Failure";
SM.Message = "Invalid UserId";
return(SM);
}
if (CL.LPID == "" || CL.LPID == null)
{
SM.Status = "Failure";
SM.Message = "Invalid LoadingId";
return(SM);
}
query = "Sp_LadingPlanWebApi";
dbcommand = new SqlCommand(query, conn);
dbcommand.CommandType = CommandType.StoredProcedure;
dbcommand.CommandTimeout = 0;
dbcommand.Parameters.AddWithValue("@QueryType", "FetchStillagesId");
dbcommand.Parameters.AddWithValue("@LPID", CL.LPID);
dbcommand.Parameters.AddWithValue("@UserId", CL.UserId);
SqlDataAdapter daGetData1 = new SqlDataAdapter(dbcommand);
DataSet dsGetData1 = new DataSet();
daGetData1.Fill(dsGetData1);
foreach (DataRow row in dsGetData1.Tables[0].Rows)
{
query = "Sp_LadingPlanWebApi";
dbcommand = new SqlCommand(query, conn);
dbcommand.CommandType = CommandType.StoredProcedure;
dbcommand.CommandTimeout = 0;
dbcommand.Parameters.AddWithValue("@QueryType", "FetchStillagesData");
dbcommand.Parameters.AddWithValue("@StillageID", row["StickerID"].ToString());
dbcommand.Parameters.AddWithValue("@UserId", CL.UserId);
dbcommand.Parameters.AddWithValue("@LoadingId", CL.LPID);
SqlDataAdapter daGetData = new SqlDataAdapter(dbcommand);
DataSet dsGetData = new DataSet();
daGetData.Fill(dsGetData);
query = "Sp_AxWebserviceIntegration";
dbcommand = new SqlCommand(query, conn);
// dbcommand.Connection.Open();
dbcommand.CommandType = CommandType.StoredProcedure;
dbcommand.CommandTimeout = 0;
SqlDataAdapter da1 = new SqlDataAdapter(dbcommand);
DataSet ds = new DataSet();
da1.Fill(ds);
// DataSet ds = CommonManger.FillDatasetWithParam("Sp_AxWebserviceIntegration");
AXWebServiceRef1.Iace_FinishedGoodServiceClient obj = new AXWebServiceRef1.Iace_FinishedGoodServiceClient();
obj.ClientCredentials.Windows.ClientCredential.Domain = Convert.ToString(ds.Tables[0].Rows[0]["Domain"]);
obj.ClientCredentials.Windows.ClientCredential.UserName = Convert.ToString(ds.Tables[0].Rows[0]["Username"]);
obj.ClientCredentials.Windows.ClientCredential.Password = Convert.ToString(ds.Tables[0].Rows[0]["Password"]);
AXWebServiceRef1.CallContext Cct = new AXWebServiceRef1.CallContext();
Cct.Company = Convert.ToString(ds.Tables[0].Rows[0]["Company"]);
Cct.Language = Convert.ToString(ds.Tables[0].Rows[0]["Language"]);
string value = obj.InsertHistoryHeaderData(Cct, Convert.ToString(dsGetData.Tables[1].Rows[0]["StillageID"]), Convert.ToString(dsGetData.Tables[0].Rows[0]["SiteID"]), Convert.ToString(dsGetData.Tables[0].Rows[0]["WorkOrderNo"]), Convert.ToString(dsGetData.Tables[0].Rows[0]["ItemId"]), Convert.ToDecimal(dsGetData.Tables[0].Rows[0]["WorkOrderQty"]));
// obj.InsertHistoryDetailData(Cct, Convert.ToString(dsGetData.Tables[1].Rows[0]["StillageID"]), "", Convert.ToString(dsGetData.Tables[1].Rows[0]["ActivityName"]), Convert.ToString(dsGetData.Tables[1].Rows[0]["ActivityDesc"]), Convert.ToString(dsGetData.Tables[1].Rows[0]["StillageLocation"]), "", "", "", "", "", "", "No", 0, "", 0, 0, Convert.ToDecimal(dsGetData.Tables[1].Rows[0]["StillageQty"]), Convert.ToString(dsGetData.Tables[1].Rows[0]["UserName"]),"QC Release",0,"","","", Convert.ToString(dsGetData.Tables[1].Rows[0]["WareHouseID"]),0);
obj.InsertHistoryDetailData(Cct, Convert.ToString(dsGetData.Tables[1].Rows[0]["StillageID"]), "", Convert.ToString(dsGetData.Tables[1].Rows[0]["ActivityName"]), Convert.ToString(dsGetData.Tables[1].Rows[0]["ActivityDesc"]), Convert.ToString(dsGetData.Tables[1].Rows[0]["StillageLocation"]), "", "", "", "", "", "", "No", 0, "", 0, 0, Convert.ToDecimal(dsGetData.Tables[1].Rows[0]["StillageQty"]), Convert.ToString(dsGetData.Tables[1].Rows[0]["UserName"]), "QC Release", 0, "", "", "", Convert.ToString(dsGetData.Tables[1].Rows[0]["WareHouseID"]));
}
query = "Sp_LadingPlanWebApi";
dbcommand = new SqlCommand(query, conn);
dbcommand.Connection.Open();
dbcommand.CommandType = CommandType.StoredProcedure;
dbcommand.Parameters.AddWithValue("@QueryType", "CancelLoadingProcess");
dbcommand.Parameters.AddWithValue("@LoadingId", CL.LPID);
dbcommand.Parameters.AddWithValue("@UserId", CL.UserId);
dbcommand.CommandTimeout = 0;
SqlDataAdapter da = new SqlDataAdapter(dbcommand);
DataTable dt = new DataTable();
da.Fill(dt);
if (dt.Rows[0]["value"].ToString() == "1")
{
SM.Status = "Success";
SM.Message = "Loading Cancel Successfully";
}
else
{
SM.Status = "Failure";
SM.Message = "Loading Cancel Failure";
}
}
catch (Exception Ex)
{
SM.Status = "Failure";
SM.Message = Ex.Message;
}
//finally
//{
// //dbcommand.Connection.Close();
//}
return(SM);
}
internal KensaIraiTblDataSet.KensaJokyoListDataTable GetDataBySearchCond(
List<string> kensaIraiHoteiKbn,
string jokasoSetchishaNm,
string jokasoShisetsuNm,
string settisyaCd,
string kensaIraiDtFrom,
string kensaIraiDtTo,
string kensaDtFrom,
string kensaDtTo)
{
SqlCommand command = CreateSqlCommand(kensaIraiHoteiKbn,
jokasoSetchishaNm,
jokasoShisetsuNm,
settisyaCd,
kensaIraiDtFrom,
kensaIraiDtTo,
kensaDtFrom,
kensaDtTo);
SqlDataAdapter adpt = new SqlDataAdapter(command);
adpt.SelectCommand.Connection = this.Connection;
KensaIraiTblDataSet.KensaJokyoListDataTable dataTable = new KensaIraiTblDataSet.KensaJokyoListDataTable();
adpt.Fill(dataTable);
return dataTable;
}
//返回时间段编码
public static string GetTimeBlockCode(string timeblock)
{
System.Data.SqlClient.SqlConnection conn1 = new System.Data.SqlClient.SqlConnection();
conn1.ConnectionString = ConnStr;
try
{
SqlCommand selectCMD1 = new SqlCommand("SELECT timeblockCode FROM timeblock where name='" + timeblock + "'", conn1);
selectCMD1.CommandTimeout = 30;
SqlDataAdapter dbDA1 = new SqlDataAdapter();
dbDA1.SelectCommand = selectCMD1;
conn1.Open();
DataSet dbDS1 = new DataSet();
dbDA1.Fill(dbDS1, "t1");
return dbDS1.Tables[0].Rows[0][0].ToString();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
conn1.Close();
}
return "";
}
internal KensaIraiTblDataSet.EnkabutsuIonNodoHikakuListDataTable GetDataBySearchCond(
string saisuiDtFrom,
string saisuiDtTo,
string saisuiinNm)
{
SqlCommand command = CreateSqlCommand(saisuiDtFrom, saisuiDtTo, saisuiinNm);
SqlDataAdapter adpt = new SqlDataAdapter(command);
adpt.SelectCommand.Connection = this.Connection;
KensaIraiTblDataSet.EnkabutsuIonNodoHikakuListDataTable dataTable = new KensaIraiTblDataSet.EnkabutsuIonNodoHikakuListDataTable();
adpt.Fill(dataTable);
return dataTable;
}
public void GetFoodTastes()
{
if (FoodTastesBtnList != null)
{
FoodTastesBtnList.RemoveAll();
FoodTastesBtnList = null;
}
FoodTastesBtnList = new FoodTastesButtonList(Panel2, 75, 45, Color.LightBlue, Color.FromArgb(255, 128, 128), 3);
System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection();
conn.ConnectionString = rms_var.ConnStr;
try
{
SqlCommand selectCMD = new SqlCommand();
selectCMD.Connection = conn;
selectCMD.CommandText = "SELECT name FROM foodtaste";
selectCMD.CommandTimeout = 30;
SqlDataAdapter dbDA = new SqlDataAdapter();
dbDA.SelectCommand = selectCMD;
conn.Open();
DataSet dbDS = new DataSet();
dbDA.Fill(dbDS, "t");
for (int i = 0; i <= dbDS.Tables[0].Rows.Count - 1; i++)
{
FoodTastesBtnList.AddNewButton(dbDS.Tables[0].Rows[i]["name"].ToString());
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
conn.Close();
}
}
private void LoadFoodTaste()
{
System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection();
conn.ConnectionString = rms_var.ConnStr;
try
{
SqlCommand selectCMD = new SqlCommand("SELECT name FROM foodtaste", conn);
selectCMD.CommandTimeout = 30;
SqlDataAdapter dbDA = new SqlDataAdapter();
dbDA.SelectCommand = selectCMD;
conn.Open();
DataSet dbDS = new DataSet();
dbDA.Fill(dbDS, "t");
//clbTaste.Items.Clear()
//For i As Integer = 0 To dbDS.Tables(0).Rows.Count - 1
//clbTaste.Items.Add(dbDS.Tables(0).Rows(i).Item(0), False)
//Next
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
conn.Close();
}
}
private void buttonSearch_Click(object sender, EventArgs e)
{
SqlConnection sqlconn = new SqlConnection();
sqlconn.ConnectionString = @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\Users\pc\source\repos\ManagementSystemSolution\StudentMS\DatabaseStudent.mdf;Integrated Security=True;Connect Timeout=30";
if (textBoxSearch.Text == "")
{
string sqlquery = "SELECT * FROM [dbo].[Student]";
try
{
sqlconn.Open();
SqlDataAdapter sda = new SqlDataAdapter(sqlquery, sqlconn);
DataSet ds = new DataSet();
sda.Fill(ds, "Student");
dataGridViewSearch.DataSource = ds.Tables["Student"];
}
catch (SqlException error)
{
MessageBox.Show(error.Message, "操作資料庫出错！", MessageBoxButtons.OK, MessageBoxIcon.Error);
textBoxSearch.Focus();
}
}
else
{
string sqlquery;
switch (comboBox1.SelectedItem.ToString())
{
case "學號":
sqlquery = String.Format("select * from [dbo].[Student] where studentNo = '{0}' ", textBoxSearch.Text);
try
{
sqlconn.Open();
DataSet ds = new DataSet();
SqlDataAdapter sda = new SqlDataAdapter(sqlquery, sqlconn);
sda.Fill(ds, "Student");
dataGridViewSearch.DataSource = ds.Tables["Student"];
if (ds.Tables[0].Rows.Count == 0)
{
MessageBox.Show("沒有查到相關訊息，請檢查查詢條件！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
}
catch (SqlException error)
{
MessageBox.Show(error.Message, "操作資料庫出错！", MessageBoxButtons.OK, MessageBoxIcon.Error);
textBoxSearch.Focus();
}
finally
{
sqlconn.Close();
}
break;
case "姓名":
sqlquery = String.Format("select * from [dbo].[Student] where StudentName LIKE N'%{0}%' ", textBoxSearch.Text);
try
{
sqlconn.Open();
DataSet ds = new DataSet();
SqlDataAdapter sda = new SqlDataAdapter(sqlquery, sqlconn);
sda.Fill(ds, "Student");
dataGridViewSearch.DataSource = ds.Tables["Student"];
if (ds.Tables[0].Rows.Count == 0)
{
MessageBox.Show("沒有查到相關訊息，請檢查查詢條件！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
}
catch (SqlException error)
{
MessageBox.Show(error.Message, "操作資料庫出错！", MessageBoxButtons.OK, MessageBoxIcon.Error);
textBoxSearch.Focus();
}
finally
{
sqlconn.Close();
}
break;
case "班級":
sqlquery = String.Format("select * from [dbo].[Student] where ClassID = '{0}' ", textBoxSearch.Text);
try
{
sqlconn.Open();
DataSet ds = new DataSet();
SqlDataAdapter sda = new SqlDataAdapter(sqlquery, sqlconn);
sda.Fill(ds, "Student");
dataGridViewSearch.DataSource = ds.Tables["Student"];
if (ds.Tables[0].Rows.Count == 0)
{
MessageBox.Show("沒有查到相關訊息，請檢查查詢條件！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
}
catch (SqlException error)
{
MessageBox.Show(error.Message, "操作資料庫出错！", MessageBoxButtons.OK, MessageBoxIcon.Error);
textBoxSearch.Focus();
}
finally
{
sqlconn.Close();
}
break;
case "科系":
sqlquery = String.Format("select * from [dbo].[Student] where StudentDep LIKE N'%{0}%' ", textBoxSearch.Text);
try
{
sqlconn.Open();
DataSet data = new DataSet();
SqlDataAdapter sda = new SqlDataAdapter(sqlquery, sqlconn);
sda.Fill(data, "Student");
dataGridViewSearch.DataSource = data.Tables["Student"];
if (data.Tables[0].Rows.Count == 0)
{
MessageBox.Show("沒有查到相關訊息，請檢查查詢條件！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
}
catch (SqlException error)
{
MessageBox.Show(error.Message, "操作資料庫出错！", MessageBoxButtons.OK, MessageBoxIcon.Error);
textBoxSearch.Focus();
}
finally
{
sqlconn.Close();
}
break;
}
}
}
public static bool EmpIdExists(string empid)
{
System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection();
conn.ConnectionString = ConnStr;
try
{
SqlCommand selectCMD = new SqlCommand("SELECT * FROM Employee where " + "EmpID=" + "'" + empid + "'", conn);
selectCMD.CommandTimeout = 30;
SqlDataAdapter dbDA = new SqlDataAdapter();
dbDA.SelectCommand = selectCMD;
conn.Open();
DataSet dbDS = new DataSet();
dbDA.Fill(dbDS, "t");
if (dbDS.Tables[0].Rows.Count > 0) //该雇员存在
{
return true;
}
else //雇员不存在
{
return false;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
return false;
}
finally
{
conn.Close();
}
}
protected void btnLogin_Click(object sender, EventArgs e)
{
try
{
DataTable dt = new DataTable();
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "usp_RegistrationLogin";
cmd.Parameters.AddWithValue("@UserName", txtUserName.Text);
cmd.Parameters.AddWithValue("@Password", crpt.Encrypt(txtPassword.Text));
cmd.Connection = conn;
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
SqlDataAdapter adp = new SqlDataAdapter(cmd);
adp.Fill(dt);
conn.Close();
if (dt.Rows.Count > 0)
{
if (Convert.ToBoolean(dt.Rows[0]["IsVerified"]) == true)
{
Session["LoginUser"] = dt.Rows[0]["FirstName"];
Session["UserId"] = dt.Rows[0]["ID"];
if (Session["Favourite"] != null)
{
int UserID = Convert.ToInt32(dt.Rows[0]["ID"]);
string MLSID = Convert.ToString(Session["Favourite"]);
int result = clsobj.Insert_Favourite(UserID, MLSID);
if ((Session["FeatureType"]) != null)
{
Response.Redirect("~/featureListing.aspx", false);
}
else
{
Response.Redirect("~/Search.aspx?PropertyType=" + Convert.ToString(Session["Type"]), false);
}
}
else if (Convert.ToString(Session["MLSID"]) != "" && Convert.ToString(Session["Type"]) != "")
{
Response.Redirect("PropertyDetails.aspx?MLSID=" + Convert.ToString(Session["MLSID"]) + "&PropertyType=" + Convert.ToString(Session["Type"]), false);
}
else
{
Response.Redirect("Home.aspx", false);
}
}
else
{
lblerror.Text = "Verify your Email to Login";
}
}
else
{
txtUserName.Text = "";
lblerror.Text = "Incorrect Username or Password";
}
}
catch (Exception ex)
{
throw ex;
}
}
public DataTable GetDatasetTable(string p_sqlCommand)
{
SqlDataAdapter daRecords = new SqlDataAdapter(p_sqlCommand, RMSGlobal.m_connectionString);
DataTable dsRecords = new DataTable();
daRecords.Fill(dsRecords);
return dsRecords;
}
private void button1_Click(object sender, EventArgs e)
{
int be;
bool bil;
bool bil1;
bool bil2;
bool bil3;
int count = 0;
Regex Sample = new Regex("^.*[^A-zА-яiЁё].*$");
Regex Sample_1 = new Regex("^[0-9]+$");
Match res1 = Sample.Match(textBox2.Text);
Match res2 = Sample.Match(textBox3.Text);
Match res3 = Sample.Match(textBox4.Text);
Match res4 = Sample_1.Match(textBox5.Text);
Match res5 = Sample_1.Match(textBox1.Text);
int a;
int b;
string query = "";
string res = "";
if (radioButton1.Checked)
{
res = "row(s) selected";
query = "select Picture.IDPicture, Picture.Place,Picture.Money,Picture.Author,Picture.Name,Name_Jenre from dbo.Picture join dbo.Jenre On ID_Jenre=ID_J";
if (checkBox1.Checked && int.TryParse(textBox1.Text, out a))
{
query += " where Picture.IDPicture = " + textBox1.Text;
}
else if (checkBox2.Checked && comboBox1.Text != "")
{
query += " where Name_Jenre=N'" + comboBox1.Text + "'";
}
}
if (radioButton2.Checked && textBox2.Text != "" && textBox3.Text != "" && textBox4.Text != "" && textBox5.Text != "" && comboBox1.Text != "" && int.TryParse(textBox3.Text, out a))
{
res = "1 row(s) inserted";
query = "INSERT INTO dbo.Picture ([Place],[Money],[Author],[Name],[ID_J]) VALUES (N'" + textBox2.Text + "',N'" + textBox3.Text + "',N'" + textBox4.Text + "',N'" + textBox5.Text + "',(select ID_Jenre from dbo.Jenre where Name_Jenre = N'" + comboBox1.Text + "'))";
bil = true;
}
else
{
bil = false;
}
if (radioButton3.Checked && textBox1.Text != "" && textBox2.Text != "" && textBox3.Text != "" && textBox4.Text != "" && textBox5.Text != "" && comboBox1.Text != "" && int.TryParse(textBox3.Text, out a) && int.TryParse(textBox1.Text, out be))
{
bil1 = true;
res = "1 row(s) updated";
query = "UPDATE dbo.Picture SET Place = N'" + textBox2.Text + "',ID_J = (select ID_Jenre from dbo.Jenre where Name_Jenre = N'" + comboBox1.Text + "'), Money = N'" + textBox3.Text + "',Author = N'" + textBox4.Text + "',Name = N'" + textBox5.Text + "' WHERE IDPicture =" + textBox1.Text;
}
else
{ bil1 = false; }
if (radioButton4.Checked && textBox1.Text != "" && int.TryParse(textBox1.Text, out a))
{
bil2 = true;
res = "1 row(s) deleted";
query = "Delete from dbo.Picture where IDPicture =" + textBox1.Text;
}
else
{ bil2 = false; }
if (bil||bil1||bil2)
{
using (SqlConnection conn = new SqlConnection(connect))
{
conn.Open();
SqlDataAdapter adapt = new SqlDataAdapter(query, conn);
DataSet data = new DataSet();
adapt.Fill(data);
dataGridView1.Visible = true;
if (radioButton1.Checked)
{
dataGridView1.DataSource = data.Tables[0];
res = res.Insert(0, data.Tables[0].Rows.Count.ToString());
textBox6.Text = res;
}
else
{
adapt = new SqlDataAdapter("select Picture.IDPicture, Picture.Place, Picture.Money, Picture.Author, Picture.Name, Name_Jenre from dbo.Picture join dbo.Jenre On ID_Jenre = ID_J;", connect);
data = new DataSet();
adapt.Fill(data);
dataGridView1.DataSource = data.Tables[0];
textBox6.Text = res;
}
conn.Close();
}
Categ();
textBox1.Text = "";
textBox2.Text = "";
textBox3.Text = "";
textBox4.Text = "";
textBox5.Text = "";
comboBox1.Text = "";
}else
{
MessageBox.Show("Помилка");
}
}
/// <summary>
/// Returns the dataset of the query.
/// </summary>
/// <param name="p_sqlCommand"></param>
/// <returns></returns>
public DataSet GetDataset(string p_sqlCommand)
{
SqlDataAdapter daRecords = new SqlDataAdapter(p_sqlCommand, RMSGlobal.m_serverConnectionString);
DataSet dsRecords = new DataSet();
daRecords.Fill(dsRecords);
return dsRecords;
}
//未结账是否允许删单据和菜品 df
public static bool AllowDeleteBillFood()
{
System.Data.SqlClient.SqlConnection conn1 = new System.Data.SqlClient.SqlConnection();
conn1.ConnectionString = ConnStr;
try
{
SqlCommand selectCMD1 = new SqlCommand();
selectCMD1.Connection = conn1;
selectCMD1.CommandText = "SELECT count(*) FROM op_acs where operatorid='" + opinfo.OpID + "' and access='df'";
selectCMD1.CommandTimeout = 30;
SqlDataAdapter dbDA1 = new SqlDataAdapter();
dbDA1.SelectCommand = selectCMD1;
conn1.Open();
DataSet dbDS1 = new DataSet();
dbDA1.Fill(dbDS1, "t");
if (Convert.ToInt32(dbDS1.Tables[0].Rows[0][0]) > 0)
{
return true;
}
else
{
return false;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
return false;
}
finally
{
conn1.Close();
}
}
public void rightmain()
{
string config = System.Web.Configuration.WebConfigurationManager
.ConnectionStrings["ProjectConnection"].ConnectionString;
SqlConnection connectionID = new SqlConnection(config);
SqlCommand commandID = new SqlCommand($"Select * FROM Country ", connectionID);
SqlDataAdapter loginAdapterID = new SqlDataAdapter(commandID);
DataTable userID = new DataTable();
loginAdapterID.Fill(userID);
//判斷Request.QueryString["id"]有沒有資料,因為一開始進網頁時要直接抓第一筆id,但網址一開始載入會沒有id資料
if (string.IsNullOrEmpty($"{Request.QueryString["id"]}"))
{
//抓名字(標題)
string id = Request.QueryString["id"] ?? $"{userID.Rows[0]["id"].ToString()}";
SqlConnection rconnection = new SqlConnection(config);
SqlCommand Rcommand = new SqlCommand($"Select * FROM Country where id =@id ", rconnection);
Rcommand.Parameters.Add("@id", SqlDbType.NVarChar); //設定參數資料型態
Rcommand.Parameters["@id"].Value = id; //接id的值變成參數的值
SqlDataAdapter loginAdapter = new SqlDataAdapter(Rcommand);
DataTable user = new DataTable();
loginAdapter.Fill(user);
rightCountryName.InnerHtml = user.Rows[0]["name"].ToString();
rightCountryName2.InnerHtml = user.Rows[0]["name"].ToString();
//內容
SqlConnection connectionMain = new SqlConnection(config);
SqlCommand commandMain = new SqlCommand($"Select * FROM Dealers where Cid=@id ", connectionMain);
commandMain.Parameters.Add("@id", SqlDbType.NVarChar); //設定參數資料型態
commandMain.Parameters["@id"].Value = id; //接id的值變成參數的值
connectionMain.Open();
SqlDataReader rereader = commandMain.ExecuteReader();
Repeater_Main.DataSource = rereader;//repeater的資料來源是從rereader來
Repeater_Main.DataBind();//執行繫結
connectionMain.Close();
}
else
{
//抓名字(標題)
SqlConnection rconnection = new SqlConnection(config);
SqlCommand Rcommand = new SqlCommand($"Select * FROM Country where id =@id ", rconnection);
Rcommand.Parameters.Add("@id", SqlDbType.NVarChar); //設定參數資料型態
Rcommand.Parameters["@id"].Value = Request.QueryString["id"]; //接id的值變成參數的值
SqlDataAdapter loginAdapter = new SqlDataAdapter(Rcommand);
DataTable user = new DataTable();
loginAdapter.Fill(user);
rightCountryName.InnerHtml = user.Rows[0]["name"].ToString();
rightCountryName2.InnerHtml = user.Rows[0]["name"].ToString();
// 繫結內容資料
SqlConnection connectionMain = new SqlConnection(config);
SqlCommand commandMain = new SqlCommand($"Select * FROM Dealers where Cid=@id ", connectionMain);
commandMain.Parameters.Add("@id", SqlDbType.NVarChar); //設定參數資料型態
commandMain.Parameters["@id"].Value = Request.QueryString["id"]; //接id的值變成參數的值
connectionMain.Open();
SqlDataReader rereader = commandMain.ExecuteReader();
Repeater_Main.DataSource = rereader;//repeater的資料來源是從rereader來
Repeater_Main.DataBind();//執行繫結
connectionMain.Close();
}
}
public string getSubchildes(int x, string UTID)
{
SqlConnection Conn = new SqlConnection(ConfigurationManager.ConnectionStrings["Con"].ConnectionString);
string SubString = "";
int k = 0;
string str = "select * from VW_MENU where PID=" + x + " order by FormID";
SqlDataAdapter da = new SqlDataAdapter(str, Conn);
DataTable dtA = new DataTable();
da.Fill(dtA);
int noOfChild = dtA.Rows.Count;
for (int i = 0; i < dtA.Rows.Count; i++)
{
if (dtA.Rows.Count == 0)
{
SubString = "";
}
else
{
string hasChild = getchildes(Convert.ToInt32(dtA.Rows[i]["id"].ToString()), UTID);
if (hasChild == "")
{
string ActiveChild = "";
string path = dtA.Rows[i]["Path"].ToString();
path = path.ToLowerInvariant();
if (PageURL.IndexOf(path) != -1)
{
ActiveChild = " class=active";
}
SubString = SubString + "<li" + ActiveChild + ">";
SubString = SubString + "<a href='" + "../" + dtA.Rows[i]["Path"].ToString() + "" + "'>";
SubString = SubString + "" + dtA.Rows[i]["FormTitle"].ToString() + "</a>";
SubString = SubString + "</li>";
}
else
{
string ActiveParent = "";
if (hasChild.IndexOf("class=active") != -1)
{
ActiveParent = " active";
}
string TotalChild = hasChild;
TotalChild = TotalChild.Substring(0, TotalChild.IndexOf("`"));
hasChild = hasChild.Substring(hasChild.IndexOf("`") + 1);
SubString = SubString + "<li class='has-sub" + ActiveParent + "'>";
SubString = SubString + "<a href='javascript:;'>";
SubString = SubString + "<b class='caret pull-right'></b>";
SubString = SubString + "<span class='badge pull-right'>" + TotalChild + "</span>";
SubString = SubString + "<i class='fa " + dtA.Rows[i]["ICON_CSS"].ToString() + "'></i>";
SubString = SubString + "<span>" + dtA.Rows[i]["FormTitle"].ToString() + "</span>";
SubString = SubString + "</a>";
SubString = SubString + "<ul class='sub-menu'>";
SubString = SubString + hasChild;
SubString = SubString + "</ul></li>";
}
}
}
if (noOfChild > 0)
{
SubString = noOfChild + "`" + SubString;
}
return(SubString);
}
public DataTable Driverlist(driverdetails d)
{
SqlConnection conn = new SqlConnection();
try {
conn.ConnectionString = ConfigurationManager.ConnectionStrings["btposdb"].ToString();
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "HVInsUpddrivers";
cmd.Connection = conn;
SqlParameter ff = new SqlParameter("@flag", SqlDbType.VarChar);
ff.Value = d.flag;
cmd.Parameters.Add(ff);
SqlParameter i = new SqlParameter("@DId", SqlDbType.Int);
i.Value = d.DId;
cmd.Parameters.Add(i);
SqlParameter di = new SqlParameter("@CountryId", SqlDbType.VarChar, 50);
di.Value = d.Country;
cmd.Parameters.Add(di);
SqlParameter n = new SqlParameter("@NAme", SqlDbType.VarChar, 50);
n.Value = d.Name;
cmd.Parameters.Add(n);
SqlParameter r = new SqlParameter("@Address", SqlDbType.VarChar, 50);
r.Value = d.Address;
cmd.Parameters.Add(r);
SqlParameter f = new SqlParameter("@PAddress", SqlDbType.VarChar, 50);
f.Value = d.PermanentAddress;
cmd.Parameters.Add(f);
SqlParameter s = new SqlParameter("@Pin", SqlDbType.VarChar, 50);
s.Value = d.Pin;
cmd.Parameters.Add(s);
SqlParameter g = new SqlParameter("@PPin", SqlDbType.VarChar, 50);
g.Value = d.PermanentPin;
cmd.Parameters.Add(g);
SqlParameter j = new SqlParameter("@PMobNo", SqlDbType.VarChar, 255);
j.Value = d.Mobilenumber;
cmd.Parameters.Add(j);
SqlParameter fr = new SqlParameter("@FirstName", SqlDbType.VarChar, 50);
fr.Value = d.FirstName;
cmd.Parameters.Add(fr);
SqlParameter lk = new SqlParameter("@LastName", SqlDbType.VarChar, 50);
lk.Value = d.LastName;
cmd.Parameters.Add(lk);
SqlParameter ed = new SqlParameter("@EmailId", SqlDbType.VarChar, 50);
ed.Value = d.EmailId;
cmd.Parameters.Add(ed);
SqlParameter dc = new SqlParameter("@DriverCode", SqlDbType.VarChar);
dc.Value = d.drivercode;
cmd.Parameters.Add(dc);
SqlParameter pr = new SqlParameter("@Photo", SqlDbType.VarChar);
pr.Value = d.Photo;
cmd.Parameters.Add(pr);
SqlParameter bg = new SqlParameter("@BloodGroup", SqlDbType.VarChar);
bg.Value = d.BloodGroup;
cmd.Parameters.Add(bg);
SqlParameter df = new SqlParameter("@DOB", SqlDbType.DateTime);
df.Value = d.DOB;
cmd.Parameters.Add(df);
SqlParameter dfs = new SqlParameter("@DOJ", SqlDbType.DateTime);
dfs.Value = d.DOJ;
cmd.Parameters.Add(dfs);
SqlParameter sd = new SqlParameter("@Status", SqlDbType.Int);
sd.Value = d.Status;
cmd.Parameters.Add(sd);
SqlParameter vf = new SqlParameter("@VehicleGroupId", SqlDbType.Int);
vf.Value = d.VehicleGroup;
cmd.Parameters.Add(vf);
SqlParameter gf = new SqlParameter("@IsVerified", SqlDbType.Int);
gf.Value = d.IsVerified;
cmd.Parameters.Add(gf);
SqlParameter hd = new SqlParameter("@IsApproved", SqlDbType.Int);
hd.Value = d.IsApproved;
cmd.Parameters.Add(hd);
SqlParameter cdd = new SqlParameter("@CurrentStateId", SqlDbType.Int);
cdd.Value = d.CurrentStateId;
cmd.Parameters.Add(cdd);
SqlParameter ptt = new SqlParameter("@PaymentTypeId", SqlDbType.Int);
ptt.Value = d.PaymentTypeId;
cmd.Parameters.Add(ptt);
DataTable dt = new DataTable();
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt);
return dt;
}
catch (Exception ex)
{
if (conn != null && conn.State == ConnectionState.Open)
{
conn.Close();
}
throw ex;
}
}
public void loaddl()
{
SqlConnection kn = new SqlConnection(@"Data Source=DESKTOP-RGHAECC;Initial Catalog=SucKhoeNhanVien_PNT;Integrated Security=True");
kn.Open();
string sql = "SELECT * from NhanVien, Khoa where NhanVien.Khoa = Khoa.IdKhoa And NhanVien.MaNV like N'" + txtmanv.Text + "' ";
// Lấy dữ liệu hành chính
SqlCommand commandsql = new SqlCommand(sql, kn); //thực thi các chức năng câu lệnh trong sql
SqlDataAdapter com = new SqlDataAdapter(commandsql); // vận chuyển dữ liệu
DataTable table = new DataTable(); //tạo một bảng ảo trong hện thống
com.Fill(table); // đổ dữ liệu vào bảng ảo
int idnhanvien = (int)table.Rows[0].ItemArray[0];
txtidnv.Text = idnhanvien.ToString();
txttennv.Text = (string)table.Rows[0]["TenNV"];
txtphongban.Text = (string)table.Rows[0]["TenKhoa"];
txtmanv.Text = (string)table.Rows[0]["MaNV"];
//Lấy dữ liệu tiền sử bệnh
//string sqlkhamtheluc = "SELECT * from LichSuBenh,TienSuBenh where LichSuBenh.Id_LichSuBenh = TienSuBenh.Id_LichSuBenh And LichSuBenh.IdNhanVien = '" + txtidnv.Text + "' And LichSuBenh.NgayKham = '" + dateTimePickerSKNV.Value.ToString("yyyy-MM-dd") + "' ";
string sqlkhamtheluc = "SELECT * from LichSuBenh,TienSuBenh where LichSuBenh.Id_LichSuBenh = TienSuBenh.Id_LichSuBenh And LichSuBenh.IdNhanVien = '" + txtidnv.Text + "'";
SqlCommand cmdsqlkhamtheluc = new SqlCommand(sqlkhamtheluc, kn);
SqlDataAdapter comsqlkhamtheluc = new SqlDataAdapter(cmdsqlkhamtheluc);
DataTable tableTSB = new DataTable();
comsqlkhamtheluc.Fill(tableTSB);
txtchieucao.Text = tableTSB.Rows[0]["ChieuCao"].ToString();
txtcannang.Text = tableTSB.Rows[0]["CanNang"].ToString();
txtBMI.Text = tableTSB.Rows[0]["ChisoBMI"].ToString();
txtmach.Text = (string)tableTSB.Rows[0]["Mach"].ToString();
txthuyetap.Text = (string)tableTSB.Rows[0]["HuyetAp"];
txtPLTL.Text = (string)tableTSB.Rows[0]["PhanLoai"];
txtTSB.Text = (string)tableTSB.Rows[0]["TienSuBenh"];
// Lấy dữ liệu Lâm sàng nội khoa
string sqlLSnoikhoa = "SELECT * from LichSuBenh,LamSang_NoiKhoa where LichSuBenh.Id_LichSuBenh = LamSang_NoiKhoa.Id_LichSuBenh And LichSuBenh.IdNhanVien = '" + txtidnv.Text + "' And LichSuBenh.NgayKham = '" + dateTimePickerSKNV.Value.ToString("yyyy-MM-dd") + "' ";
SqlCommand cmdsqlLSnoikhoa = new SqlCommand(sqlLSnoikhoa, kn);
SqlDataAdapter comsqlLSnoikhoa = new SqlDataAdapter(cmdsqlLSnoikhoa);
DataTable tableLSNK = new DataTable();
comsqlLSnoikhoa.Fill(tableLSNK);
txtPLtuanhoan.Text = (string)tableLSNK.Rows[0]["PL_TuanHoan"];
txtPLhohap.Text = (string)tableLSNK.Rows[0]["PL_HoHap"];
txtPLtieuhoa.Text = (string)tableLSNK.Rows[0]["PL_TieuHoa"];
txtPLthan.Text = (string)tableLSNK.Rows[0]["PL_ThanTietNieu"];
txtPLNoiTiet.Text = (string)tableLSNK.Rows[0]["PL_NoiTiet"];
txtBSKham.Text = (string)tableLSNK.Rows[0]["GhiChu"];
// Lấy dữ liệu lâm sàng da liễu
string sqlLSDL = "SELECT * from LamSang_DaLieu, LichSuBenh where LichSuBenh.Id_LichSuBenh = LamSang_DaLieu.Id_LichSuBenh And LichSuBenh.IdNhanVien = '" + txtidnv.Text + "' And LichSuBenh.NgayKham = '" + dateTimePickerSKNV.Value.ToString("yyyy-MM-dd") + "' ";
SqlCommand cmdsqlLSDL = new SqlCommand(sqlLSDL, kn);
SqlDataAdapter comsqlLSDL = new SqlDataAdapter(cmdsqlLSDL);
DataTable tblLSDL = new DataTable();
comsqlLSDL.Fill(tblLSDL);
txtPLxuongkhop.Text = (string)tblLSDL.Rows[0]["PL_CoXuongKhop"];
txtPLthankinh.Text = (string)tblLSDL.Rows[0]["PL_ThanKinh"];
txtPLtamthan.Text = (string)tblLSDL.Rows[0]["PL_TamThan"];
txtPLdalieu.Text = (string)tblLSDL.Rows[0]["PL_DaLieu"];
txtBSkhamDL.Text = (string)tblLSDL.Rows[0]["TenBS"];
// Lấy dữ liệu lâm sàng tai mũi họng
string sqlLSTMH = "SELECT * from LichSuBenh,LamSang_TaiMuiHong where LichSuBenh.Id_LichSuBenh = LamSang_TaiMuiHong.Id_LichSuBenh And LichSuBenh.IdNhanVien = '" + txtidnv.Text + "' And LichSuBenh.NgayKham = '" + dateTimePickerSKNV.Value.ToString("yyyy-MM-dd") + "' ";
SqlCommand cmdsqlLSTMH = new SqlCommand(sqlLSTMH, kn);
SqlDataAdapter comsqlLSTMH = new SqlDataAdapter(cmdsqlLSTMH);
DataTable tblLSTMH = new DataTable();
comsqlLSTMH.Fill(tblLSTMH);
txttraithuong.Text = (string)tblLSTMH.Rows[0]["TaiTrai_NoiThuong"];
txtphaithuong.Text = (string)tblLSTMH.Rows[0]["TaiPhai_NoiThuong"];
txtphaitham.Text = (string)tblLSTMH.Rows[0]["TaiPhai_NoiTham"];
txttraitham.Text = (string)tblLSTMH.Rows[0]["TaiTrai_NoiTham"];
txtBenhMHkhac.Text = (string)tblLSTMH.Rows[0]["BenhTMH"];
txtPLmuihong.Text = (string)tblLSTMH.Rows[0]["PhanLoai"];
txtBSmuihong.Text = (string)tblLSTMH.Rows[0]["BSKham"];
// Lấy dữ liệu lâm sàng răng hàm mặt
string sqlLSRHM = "SELECT * from LamSang_RangHam,LichSuBenh where LichSuBenh.Id_LichSuBenh = LamSang_RangHam.Id_LichSuBenh And LichSuBenh.IdNhanVien = '" + txtidnv.Text + "' And LichSuBenh.NgayKham = '" + dateTimePickerSKNV.Value.ToString("yyyy-MM-dd") + "' ";
SqlCommand cmdsqlLSRHM = new SqlCommand(sqlLSRHM, kn);
SqlDataAdapter comsqlLSRHM = new SqlDataAdapter(cmdsqlLSRHM);
DataTable tblLSRHM = new DataTable();
comsqlLSRHM.Fill(tblLSRHM);
txthamtren.Text = (string)tblLSRHM.Rows[0]["HamTren"];
txthamduoi.Text = (string)tblLSRHM.Rows[0]["HamDuoi"];
txtBenhrangkhac.Text = (string)tblLSRHM.Rows[0]["BenhVeRang"];
txtPLrang.Text = (string)tblLSRHM.Rows[0]["PhanLoai"];
txtBSRang.Text = (string)tblLSRHM.Rows[0]["BSKham"];
// Lấy dữ liệu lâm sàng mắt
string sqlLSMat = "Select * from LichSuBenh,LamSang_Mat where LichSuBenh.Id_LichSuBenh = LamSang_Mat.Id_LichSuBenh And LichSuBenh.IdNhanVien = '" + txtidnv.Text + "' And LichSuBenh.NgayKham = '" + dateTimePickerSKNV.Value.ToString("yyyy-MM-dd") + "' ";
SqlCommand cmdsqlMat = new SqlCommand(sqlLSMat, kn);
SqlDataAdapter comsqlMat = new SqlDataAdapter(cmdsqlMat);
DataTable tblLSMat = new DataTable();
comsqlMat.Fill(tblLSMat);
txtkokinhphai.Text = (string)tblLSMat.Rows[0]["KhongKinhPhai"];
txtkokinhtrai.Text = (string)tblLSMat.Rows[0]["KhongKinhTrai"];
txtcokinhphai.Text = (string)tblLSMat.Rows[0]["KinhPhai"];
txtcokinhtrai.Text = (string)tblLSMat.Rows[0]["KinhTrai"];
txtbenhmatkhac.Text = (string)tblLSMat.Rows[0]["BenhMat"];
txtPLmat.Text = (string)tblLSMat.Rows[0]["PhanLoai"];
txtBSmat.Text = (string)tblLSMat.Rows[0]["BacSiKham"];
// Lấy dữ liệu phụ khoa
string sqlLSPK = "Select * from LichSuBenh,LamSang_PhuKhoa where LichSuBenh.Id_LichSuBenh = LamSang_PhuKhoa.Id_LichSuBenh And LichSuBenh.IdNhanVien = '" + txtidnv.Text + "' And LichSuBenh.NgayKham = '" + dateTimePickerSKNV.Value.ToString("yyyy-MM-dd") + "' ";
SqlCommand cmdsqlLSPK = new SqlCommand(sqlLSPK, kn);
SqlDataAdapter comsqlLSPK = new SqlDataAdapter(cmdsqlLSPK);
DataTable tblLSPK = new DataTable();
comsqlLSPK.Fill(tblLSPK);
txtpara.Text = (string)tblLSPK.Rows[0]["SanPK"];
txtPLphukhoa.Text = (string)tblLSPK.Rows[0]["PhanLoai"];
//txtghichu.Text = (string)tblLSPK.Rows[0]["GhiChu"];
txtghichu.Text = (tblLSPK.Rows[0]["GhiChu"] == null) ? string.Empty : tblLSPK.Rows[0]["GhiChu"].ToString();
txtBSphukhoa.Text = (string)tblLSPK.Rows[0]["BSKham"];
//Lấy dữ liệu cận lâm sàng
string sqlCLS = "select* from LichSuBenh, CanLamSang where LichSuBenh.Id_LichSuBenh = CanLamSang.Id_LichSuBenh And LichSuBenh.IdNhanVien = '" + txtidnv.Text + "' And LichSuBenh.NgayKham = '" + dateTimePickerSKNV.Value.ToString("yyyy-MM-dd") + "' ";
SqlCommand cmdsqlCLS = new SqlCommand(sqlCLS, kn);
SqlDataAdapter comsqlCLS = new SqlDataAdapter(cmdsqlCLS);
DataTable tblCLS = new DataTable();
comsqlCLS.Fill(tblCLS);
txtkqCLS.Text = (string)tblCLS.Rows[0]["KetQua"];
txtdanhgia.Text = (string)tblCLS.Rows[0]["DanhGia"];
txtBSCLS.Text = (string)tblCLS.Rows[0]["BsKham"];
// Lấy dữ liệu lịch sử bệnh
string sqlLSB = "select* from LichSuBenh where LichSuBenh.IdNhanVien = '" + txtidnv.Text + "' And LichSuBenh.NgayKham = '" + dateTimePickerSKNV.Value.ToString("yyyy-MM-dd") + "' ";
SqlCommand cmdsqlLSB = new SqlCommand(sqlLSB, kn);
SqlDataAdapter comsqlLSB = new SqlDataAdapter(cmdsqlLSB);
DataTable tblLSB = new DataTable();
comsqlLSB.Fill(tblLSB);
//var date = (DateTime)tblLSB.Rows[0]["NgayKham"];
//dateTimePickerSKNV.Value = date;
txtPLSucKhoe.Text = (string)tblLSB.Rows[0]["PhanLoaiSK"];
txtbenhkhac.Text = (string)tblLSB.Rows[0]["BenhKhac"];
txtBSKetLuan.Text = (string)tblLSB.Rows[0]["GhiChu"];
}
public DataTable SaveDriverDoc(DriverDocuments a)
{
//connect to database
SqlConnection conn = new SqlConnection();
DataTable dt = new DataTable();
try
{
//connetionString="Data Source=ServerName;Initial Catalog=DatabaseName;User ID=UserName;Password=Password"
conn.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["btposdb"].ToString();
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "HVInsUpdDelDriverDocs";
cmd.Connection = conn;
SqlParameter id = new SqlParameter("@Id", SqlDbType.Int);
id.Value = a.Id;
cmd.Parameters.Add(id);
SqlParameter AssetId = new SqlParameter("@DriverId", SqlDbType.Int);
AssetId.Value = a.DriverId;
cmd.Parameters.Add(AssetId);
SqlParameter Gid = new SqlParameter("@FileName", SqlDbType.VarChar, 100);
Gid.Value = a.docName;
cmd.Parameters.Add(Gid);
SqlParameter rootassetid = new SqlParameter("@DocTypeId", SqlDbType.Int);
rootassetid.Value = a.docTypeId;
cmd.Parameters.Add(rootassetid);
SqlParameter AsstMDLHierarID = new SqlParameter("@UpdatedById", SqlDbType.Int);
AsstMDLHierarID.Value = a.UpdatedById;
cmd.Parameters.Add(AsstMDLHierarID);
SqlParameter assetModelId = new SqlParameter("@ExpiryDate", SqlDbType.Date);
assetModelId.Value = a.expiryDate;
cmd.Parameters.Add(assetModelId);
SqlParameter LocationId = new SqlParameter("@DueDate", SqlDbType.Date);
LocationId.Value = a.dueDate;
cmd.Parameters.Add(LocationId);
SqlParameter parentid = new SqlParameter("@FileContent", SqlDbType.VarChar);
parentid.Value = a.docContent;
cmd.Parameters.Add(parentid);
SqlParameter flag = new SqlParameter("@change", SqlDbType.VarChar);
flag.Value = a.insupddelflag;
cmd.Parameters.Add(flag);
SqlParameter loggedinUserId1 = new SqlParameter("@loggedinUserId", SqlDbType.Int);
loggedinUserId1.Value = a.UpdatedById;
cmd.Parameters.Add(loggedinUserId1);
SqlParameter doc = new SqlParameter("@DocumentNo", SqlDbType.VarChar, 50);
doc.Value = a.DocumentNo;
cmd.Parameters.Add(doc);
SqlParameter doc2 = new SqlParameter("@DocumentNo2", SqlDbType.VarChar, 50);
doc2.Value = a.DocumentNo2;
cmd.Parameters.Add(doc2);
SqlParameter ver = new SqlParameter("@IsVerified", SqlDbType.Int);
ver.Value = a.isVerified;
cmd.Parameters.Add(ver);
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt);
return dt;
}
catch (Exception ex)
{
if (conn != null && conn.State == ConnectionState.Open)
{
conn.Close();
}
throw ex;
}
}
protected void Page_Load(object sender, EventArgs e)
{
string var_id = Request.QueryString["id"];
string action_id = Request.QueryString["action"];
//manage page content
if (var_id != null)
{
LabsList_Form.Visible = false;
if (var_id == "new")
{
if (Session["teacher"] == null)
{
Response.Redirect("~/Content/Labs.aspx");
}
NewLab_Form.Visible = true;
LabsList.Visible = false;
DisplayLab_Content.Visible = true;
}
else
{
if (Session["teacher"] != null)
{
DisplayLab_AddLab.Visible = true;
DisplayLab_DeleteLab.Visible = true;
DisplayLab_AddPage.Visible = true;
DisplayLab_EditPage.Visible = true;
DisplayLab_DeletePage.Visible = true;
}
string SQL_SELECT = "SELECT * FROM " + LabsDB + " WHERE LabIndex=" + var_id;
SqlCommand CMD_SELECT = new SqlCommand(SQL_SELECT, DB_Connection);
CMD_SELECT.CommandType = CommandType.Text;
string SQL_SELECT_A = "SELECT count(*) FROM " + LabsDB + " WHERE LabIndex=" + var_id;
SqlCommand CMD_SELECT_A = new SqlCommand(SQL_SELECT_A, DB_Connection);
CMD_SELECT_A.CommandType = CommandType.Text;
string SQL_SELECT_P = "SELECT LabIndex FROM " + LabsDB + " WHERE LabIndex=" + (Convert.ToInt32(var_id) - 1);
SqlCommand CMD_SELECT_P = new SqlCommand(SQL_SELECT_P, DB_Connection);
CMD_SELECT_P.CommandType = CommandType.Text;
string SQL_SELECT_N = "SELECT LabIndex FROM " + LabsDB + " WHERE LabIndex=" + (Convert.ToInt32(var_id) + 1);
SqlCommand CMD_SELECT_N = new SqlCommand(SQL_SELECT_N, DB_Connection);
CMD_SELECT_P.CommandType = CommandType.Text;
DB_Connection.Open();
using (SqlDataReader Reader = CMD_SELECT.ExecuteReader())
{
if (Reader.Read() && !NewLab_Form.Visible) //2nd check correct [?]
{
DisplayLab_Form.Visible = true;
DisplayLab_Title.Text = Reader["Title"].ToString();
DisplayLab_Subtitle.Text = Reader["Subtitle"].ToString();
DisplayLab_DataSource.SelectCommand = "SELECT Content FROM " + LabsDB + " WHERE LabIndex=" + var_id;
}
}
// disable "Delete Page" button if only 1 page found
if (Convert.ToInt32(CMD_SELECT_A.ExecuteScalar().ToString()) < 2)
{
DisplayLab_DeletePage.Enabled = false;
DisplayLab_DeletePage.ImageUrl = "~/Source/img/document-remove-disabled.png";
DisplayLab_DeletePage.ToolTip = "";
}
// display previous Lab button
using (SqlDataReader Reader2 = CMD_SELECT_P.ExecuteReader())
{
if (Reader2.Read() && Reader2.HasRows)
{
DisplayLab_Previous.NavigateUrl = "~/Content/Labs.aspx?id=" + Reader2["LabIndex"].ToString();
}
else
{
DisplayLab_Previous.Visible = false;
}
Reader2.Close();
}
// display next Lab button
using (SqlDataReader Reader3 = CMD_SELECT_N.ExecuteReader())
{
if (Reader3.Read() && Reader3.HasRows)
{
DisplayLab_Next.NavigateUrl = "~/Content/Labs.aspx?id=" + Reader3["LabIndex"].ToString();
}
else
{
DisplayLab_Next.Visible = false;
}
Reader3.Close();
}
LabsList.Visible = false;
DB_Connection.Close();
}
}
else //display list of Labs
{
//need to prevent postback callback reimplementation
if (Page.IsPostBack)
{
return;
}
LabsList.Visible = true;
Button_Return.Visible = false;
if (Session["teacher"] == null)
{
LabsList.Columns[4].Visible = false;
LabsList.ShowFooter = false;
}
string SQL_SELECT = "SELECT Surname, Name, LabIndex, DateCreated, Title FROM " + LabsDB + " INNER JOIN " + UsersDB + " ON " + LabsDB + ".AuthorID=" + UsersDB + ".Username WHERE Page=1";
SqlDataAdapter da = new SqlDataAdapter(SQL_SELECT, DB_Connection);
DataTable dt = new DataTable();
DB_Connection.Open();
da.Fill(dt);
LabsList.DataSource = dt;
LabsList.DataBind();
DB_Connection.Close();
}
}
public DataTable Bankingdetails(bankdetails b)
{
SqlConnection conn = new SqlConnection();
try
{
conn.ConnectionString = ConfigurationManager.ConnectionStrings["btposdb"].ToString();
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "HVInsUpdBankingdetails";
cmd.Connection = conn;
SqlParameter ff = new SqlParameter("@flag", SqlDbType.VarChar);
ff.Value = b.flag;
cmd.Parameters.Add(ff);
SqlParameter i = new SqlParameter("@Id", SqlDbType.Int);
i.Value = b.Id;
cmd.Parameters.Add(i);
SqlParameter n = new SqlParameter("@Accountnumber", SqlDbType.VarChar, 50);
n.Value = b.Accountnumber;
cmd.Parameters.Add(n);
SqlParameter r = new SqlParameter("@BankName", SqlDbType.VarChar, 50);
r.Value = b.BankName;
cmd.Parameters.Add(r);
SqlParameter a = new SqlParameter("@BankCode", SqlDbType.VarChar, 50);
a.Value = b.Bankcode;
cmd.Parameters.Add(a);
SqlParameter s = new SqlParameter("@BranchAddress", SqlDbType.VarChar, 50);
s.Value = b.BranchAddress;
cmd.Parameters.Add(s);
SqlParameter j2 = new SqlParameter("@CountryId", SqlDbType.VarChar,50);
j2.Value = b.Country;
cmd.Parameters.Add(j2);
SqlParameter f = new SqlParameter("@IsActive", SqlDbType.Int);
f.Value = b.IsActive;
cmd.Parameters.Add(f);
SqlParameter ddd = new SqlParameter("@DriverId", SqlDbType.Int);
ddd.Value = b.DriverId;
cmd.Parameters.Add(ddd);
SqlParameter er = new SqlParameter("@QRCode", SqlDbType.VarChar);
er.Value = b.qrcode;
cmd.Parameters.Add(er);
SqlParameter Code1 = new SqlParameter("@Code", SqlDbType.VarChar);
Code1.Value = b.DriverCode;
cmd.Parameters.Add(Code1);
DataTable dt = new DataTable();
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt);
//update the qr code
if (dt.Rows.Count > 0) {
i.Value = dt.Rows[0]["Id"].ToString();
var code = dt.Rows[0]["Code"].ToString();
if (code != "") {
ff.Value = "U";
var qrcode = GetQRCode(code);
er.Value = qrcode;
Code1.Value = code;
dt = new DataTable();
da.Fill(dt);
}
}
return dt;
}
catch (Exception ex)
{
if (conn != null && conn.State == ConnectionState.Open)
{
conn.Close();
}
throw ex;
}
}
protected void Page_Load(object sender, EventArgs e)
{
base.AllowHttpMethod("POST");
base.DisableTop(true);
int pgNow;
int pgSize;
string SearchDateS;
string SearchDateE;
int SearchKind = 0;
int UserID = 0;
SearchDateS = Request.Form["SearchDateS"] ?? "";
SearchDateE = Request.Form["SearchDateE"] ?? "";
if (SearchDateS != "")
{
try
{
SearchDateS = Convert.ToDateTime((Convert.ToInt32(SearchDateS.Split('-')[0]) + 1911).ToString() + "/" + SearchDateS.Split('-')[1] + "/01").ToString("yyyy/MM/dd");
}
catch
{
SearchDateS = "";
}
}
if (SearchDateE != "")
{
try
{
SearchDateE = Convert.ToDateTime((Convert.ToInt32(SearchDateE.Split('-')[0]) + 1911).ToString() + "/" + SearchDateE.Split('-')[1] + "/01").ToString("yyyy/MM/dd");
SearchDateE = Convert.ToDateTime(SearchDateE).AddMonths(1).AddDays(-1).ToString("yyyy/MM/dd");
}
catch
{
SearchDateE = "";
}
}
int.TryParse(Request.Form["UserID"], out UserID);
int.TryParse(Request.Form["pgNow"], out pgNow);
int.TryParse(Request.Form["pgSize"], out pgSize);
int.TryParse(Request.Form["SearchKind"], out SearchKind);
List <SearchCheckListVM> list = new List <SearchCheckListVM>();
PageVM rtn = new PageVM();
DataSet ds = new DataSet();
try
{
using (SqlConnection sc = new SqlConnection(WebConfigurationManager.ConnectionStrings["ConnDB"].ToString()))
{
using (SqlCommand cmd = new SqlCommand("dbo.usp_SearchCheck_xGetHistoryAuditList", sc))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@pgNow", pgNow == 0 ? 1 : pgNow);
cmd.Parameters.AddWithValue("@pgSize", pgSize == 0 ? 10 : pgSize);
cmd.Parameters.AddWithValue("@SearchDateS", SearchDateS);
cmd.Parameters.AddWithValue("@SearchDateE", SearchDateE);
cmd.Parameters.AddWithValue("@SearchKind", SearchKind);
cmd.Parameters.AddWithValue("@UserID", UserID);
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
sc.Open();
da.Fill(ds);
}
}
}
EntityS.FillModel(list, ds.Tables[0]);
EntityS.FillModel(rtn, ds.Tables[1]);
}
catch (Exception ex)
{
Response.Write(ex.Message + ex.StackTrace);
}
rtn.message = list;
Response.ContentType = "application/json; charset=utf-8";
Response.Write(Newtonsoft.Json.JsonConvert.SerializeObject(rtn));
Response.End();
}
//获取时间段，返回dataview
public static DataTable LoadTimeBlock()
{
System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection();
conn.ConnectionString = ConnStr;
try
{
SqlCommand selectCMD = new SqlCommand("SELECT name FROM timeblock", conn);
selectCMD.CommandTimeout = 30;
SqlDataAdapter dbDA = new SqlDataAdapter();
dbDA.SelectCommand = selectCMD;
conn.Open();
DataSet dbDS = new DataSet();
dbDA.Fill(dbDS, "TimeBlockList");
return dbDS.Tables[0];
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
conn.Close();
}
return null;
}
public List <sosportalconfiguracaocliente_Dommain> ListarTodos()
{
try
{
var _connectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString();
/* Criar conexão com banco de dados */
var _connection = new SqlConnection(_connectionString);
/* Abrir conexão */
_connection.Open();
/* Criar comando que vai enviar informações para o banco */
SqlCommand _command = _connection.CreateCommand();
/* Adicionar as informações dentro do comando que vai enviar para o banco */
_command.CommandType = CommandType.StoredProcedure;
/* Recebe o nome da procedure que esta sendo executada */
_command.CommandText = "Usp_ListarTodos_ConfigurcaoCliente";
/* Defini o tempo que a conexão ficará aberta (Em Segundos [7200] = 2 horas) */
_command.CommandTimeout = 7200;
/* Criar um adptador */
var sqlDataAdapter = new SqlDataAdapter(_command);
/* Criar datatable vasia aonde vou adicionar os valores que serão retornados do banco */
var datatable = new DataTable();
/* Mandar comando ir ate o banco buscar os daods e o o adptador preencher a datatable */
sqlDataAdapter.Fill(datatable);
/* Finaliza Conexão com banco de Dados */
_connection.Dispose();
_connection.Close();
var retorno = new List <sosportalconfiguracaocliente_Dommain>();
/* O foreach varre o datarow e adiciona cada linha em uma lista ate acabar coleção */
foreach (DataRow linha in datatable.Rows)
{
//Adiciona as informações namespace entidade
var entity = new sosportalconfiguracaocliente_Dommain
{
IdConfiguracaoCliente = int.Parse(linha["IdConfiguracaoCliente"].ToString()),
IdPerfil = int.Parse(linha["IdPerfil"].ToString()),
IdCliente = int.Parse(linha["IdCliente"].ToString()),
IdConfiguracao = int.Parse(linha["IdCOnfiguracao"].ToString()),
Status = bool.Parse(linha["Status"].ToString()),
};
retorno.Add(entity);
}
/* Retorna a tabela preenchida */
return(retorno);
}
catch (System.Data.SqlClient.SqlException exception)
{
throw new Exception(message:
exception.Number.ToString() + " - " + exception.Message.ToString() + " - " +
exception.InnerException.ToString());
}
catch (Exception exception)
{
throw new Exception(exception.ToString());
}
}
private void lgnbtn_Click(object sender, EventArgs e)
{
SqlConnection MyConnection = new SqlConnection(@"Data Source=DESKTOP-S9RU5TT\USMAN;Initial Catalog=LMS;Integrated Security=True");
MyConnection.Open();
SqlCommand cmd;
cmd = MyConnection.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT User_ID,Username,Password,Type from [dbo].[User]";
cmd.ExecuteNonQuery();
DataTable dt = new DataTable();
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt);
int x = 0;
String s = "";
String usrID = "";
foreach (DataRow dr in dt.Rows)
{
s = dr["Username"].ToString();
String t = dr["Password"].ToString();
String us = dr["Type"].ToString();
usrID = dr["User_ID"].ToString();
if (s == username.Text && t == password.Text && us == "admin")
{
x = 1;
break;
}
else if (s == username.Text && t == password.Text && us == "staff")
{
x = 2;
break;
}
}
if (x == 1)
{
User u = new User();
u.UserName = s;
users = s;
userID = usrID;
// session["userName"] = u.UserName;
MessageBox.Show("Welcome " + users);
openchildform(new Formmain());
}
else if (x == 2)
{
User u = new User();
u.UserName = s;
users = s;
userID = usrID;
// session["userName"] = u.UserName;
MessageBox.Show("Welcome " + users);
openchildform(new Formstf());
}
else
{
MessageBox.Show("Incorrect Username or Password");
}
}
protected void Page_Load(object sender, EventArgs e)
{
string sr = Request.QueryString["type"];
string rep = "";
sr = sr.ToLower();
string s = sr.Trim('\'');
string ans = "no";
if (s != "")
{
SqlCommand cmd = new SqlCommand("Insert into Ques(Question,Date,Time) values('" + s + "','" + DateTime.Now.ToShortDateString() + "','" + DateTime.Now.ToShortTimeString() + "')", con);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
Session["action"] = "added";
if (s.Equals("hi") || s.Equals("hii") || s.Equals("hiii") || s.Equals("hiiii") || s.Equals("hhii") || s.Equals("hello") || s.Equals("helloo") || s.Equals("helo") || s.Equals("heello") || s.Equals("helllo") || s.Equals("helloooo"))
{
ans = "OK";
SqlDataAdapter da = new SqlDataAdapter("Select top 1 * from generalreply order by NEWID()", con);
DataSet ds = new DataSet();
da.Fill(ds);
rep = Convert.ToString(ds.Tables[0].Rows[0][1]);
string del = "delete from Answer";
SqlCommand dcmd = new SqlCommand(del, con);
con.Open();
dcmd.ExecuteNonQuery();
con.Close();
SqlCommand cmd1 = new SqlCommand("Insert into Answer(Question,Answer) values('" + s + "','" + rep + "')", con);
con.Open();
cmd1.ExecuteNonQuery();
con.Close();
Response.Redirect("bot.aspx?reply=" + rep + "");
}
else if (s.Contains("i") && s.Contains("fine") || s.Contains("i") && s.Contains("good"))
{
ans = "OK";
SqlDataAdapter da = new SqlDataAdapter("Select top 1 * from addqueries order by NEWID()", con);
DataSet ds = new DataSet();
da.Fill(ds);
rep = Convert.ToString(ds.Tables[0].Rows[0][1]);
string del = "delete from Answer";
SqlCommand dcmd = new SqlCommand(del, con);
con.Open();
dcmd.ExecuteNonQuery();
con.Close();
SqlCommand cmd1 = new SqlCommand("Insert into Answer(Question,Answer) values('" + s + "','" + rep + "')", con);
con.Open();
cmd1.ExecuteNonQuery();
con.Close();
Response.Redirect("bot.aspx?reply=" + rep + "");
}
//if (s.Contains("you") && s.Contains("how") || s.Contains("you") && s.Contains("how") && s.Contains("do"))
//{
// ans = "OK";
// rep = "I am Fine..How Are You ?";
// string del = "delete from Answer";
// SqlCommand dcmd = new SqlCommand(del, con);
// con.Open();
// dcmd.ExecuteNonQuery();
// con.Close();
// SqlCommand cmd1 = new SqlCommand("Insert into Answer(Question,Answer) values('" + s + "','" + rep + "')", con);
// con.Open();
// cmd1.ExecuteNonQuery();
// con.Close();
// Response.Redirect("bot.aspx?reply=" + rep + "");
//}
else if (s.Contains("timetable") || s.Contains("time"))
{
ans = "OK";
String s2, k1, k2, k3, rep1 = "s", link = "";
int count;
SqlDataAdapter da4 = new SqlDataAdapter("Select * from timetable where keywords = '2'", con);
DataSet ds4 = new DataSet();
da4.Fill(ds4);
count = Convert.ToInt32(ds4.Tables[0].Rows.Count);
for (int i = 0; i < count; i++)
{
k1 = Convert.ToString(ds4.Tables[0].Rows[i][2]).ToLower();
k2 = Convert.ToString(ds4.Tables[0].Rows[i][3]).ToLower();
k3 = Convert.ToString(ds4.Tables[0].Rows[i][4]).ToLower();
if (s.Contains(k1) && s.Contains(k2) && s.Contains(k3))
{
rep1 = Convert.ToString(ds4.Tables[0].Rows[i][7]);
rep = rep1;
link = Convert.ToString(ds4.Tables[0].Rows[i][8]);
string del = "delete from Answer";
SqlCommand dcmd = new SqlCommand(del, con);
con.Open();
dcmd.ExecuteNonQuery();
con.Close();
SqlCommand cmd1 = new SqlCommand("Insert into Answer(Question,Answer,Link) values('" + s + "','" + rep + "','" + link + "')", con);
con.Open();
cmd1.ExecuteNonQuery();
con.Close();
Response.Redirect("bot.aspx?reply=" + rep + "&link=" + link + "");
}
}
if (rep1 == "s")
{
da4 = new SqlDataAdapter("Select * from timetable where keywords = '2' order by newid()", con);
ds4 = new DataSet();
da4.Fill(ds4);
rep = Convert.ToString(ds4.Tables[0].Rows[0][7]);
link = Convert.ToString(ds4.Tables[0].Rows[0][8]);
string del = "delete from Answer";
SqlCommand dcmd = new SqlCommand(del, con);
con.Open();
dcmd.ExecuteNonQuery();
con.Close();
SqlCommand cmd1 = new SqlCommand("Insert into Answer(Question,Answer,Link) values('" + s + "','" + rep + "','" + link + "')", con);
con.Open();
cmd1.ExecuteNonQuery();
con.Close();
Response.Redirect("bot.aspx?reply=" + rep + "&link=" + link + "");
}
}
else if (s.Contains("question papers") || s.Contains("question paper"))
{
ans = "OK";
String s2, k1, k2, rep1 = "s", link = "";
int count;
SqlDataAdapter da3 = new SqlDataAdapter("Select * from QuestionPapers where keywords = '2'", con);
DataSet ds3 = new DataSet();
da3.Fill(ds3);
count = Convert.ToInt32(ds3.Tables[0].Rows.Count);
for (int i = 0; i < count; i++)
{
k1 = Convert.ToString(ds3.Tables[0].Rows[i][1]).ToLower();
k2 = Convert.ToString(ds3.Tables[0].Rows[i][2]).ToLower();
if (s.Contains(k1) && s.Contains(k2))
{
rep1 = Convert.ToString(ds3.Tables[0].Rows[i][6]);
rep = rep1;
link = Convert.ToString(ds3.Tables[0].Rows[i][7]);
string del = "delete from Answer";
SqlCommand dcmd = new SqlCommand(del, con);
con.Open();
dcmd.ExecuteNonQuery();
con.Close();
SqlCommand cmd1 = new SqlCommand("Insert into Answer(Question,Answer,Link) values('" + s + "','" + rep + "','" + link + "')", con);
con.Open();
cmd1.ExecuteNonQuery();
con.Close();
Response.Redirect("bot.aspx?reply=" + rep + "&link=" + link + "");
}
}
if (rep1 == "s")
{
da3 = new SqlDataAdapter("Select * from QuestionPapers where keywords = '2' order by newid()", con);
ds3 = new DataSet();
da3.Fill(ds3);
rep = Convert.ToString(ds3.Tables[0].Rows[0][6]);
link = Convert.ToString(ds3.Tables[0].Rows[0][7]);
string del = "delete from Answer";
SqlCommand dcmd = new SqlCommand(del, con);
con.Open();
dcmd.ExecuteNonQuery();
con.Close();
SqlCommand cmd1 = new SqlCommand("Insert into Answer(Question,Answer,Link) values('" + s + "','" + rep + "','" + link + "')", con);
con.Open();
cmd1.ExecuteNonQuery();
con.Close();
Response.Redirect("bot.aspx?reply=" + rep + "&link=" + link + "");
}
}
else if (s.Contains("reference book"))
{
ans = "OK";
String s2, k1, k2, rep1 = "s";
int count;
SqlDataAdapter da3 = new SqlDataAdapter("Select * from ReferenceBook where keywords = '2'", con);
DataSet ds3 = new DataSet();
da3.Fill(ds3);
count = Convert.ToInt32(ds3.Tables[0].Rows.Count);
for (int i = 0; i < count; i++)
{
k1 = Convert.ToString(ds3.Tables[0].Rows[i][1]).ToLower();
k2 = Convert.ToString(ds3.Tables[0].Rows[i][2]).ToLower();
// k3 = Convert.ToString(ds3.Tables[0].Rows[i][2]).ToLower();
if (s.Contains(k1) && s.Contains(k2))
{
rep1 = Convert.ToString(ds3.Tables[0].Rows[i][6]);
rep = rep1;
string del = "delete from Answer";
SqlCommand dcmd = new SqlCommand(del, con);
con.Open();
dcmd.ExecuteNonQuery();
con.Close();
SqlCommand cmd1 = new SqlCommand("Insert into Answer(Question,Answer) values('" + s + "','" + rep + "')", con);
con.Open();
cmd1.ExecuteNonQuery();
con.Close();
Response.Redirect("bot.aspx?reply=" + rep + "");
}
}
if (rep1 == "s")
{
da3 = new SqlDataAdapter("Select * from ReferenceBook where keywords = '2' order by newid()", con);
ds3 = new DataSet();
da3.Fill(ds3);
rep = Convert.ToString(ds3.Tables[0].Rows[0][6]);
string del = "delete from Answer";
SqlCommand dcmd = new SqlCommand(del, con);
con.Open();
dcmd.ExecuteNonQuery();
con.Close();
SqlCommand cmd1 = new SqlCommand("Insert into Answer(Question,Answer) values('" + s + "','" + rep + "')", con);
con.Open();
cmd1.ExecuteNonQuery();
con.Close();
Response.Redirect("bot.aspx?reply=" + rep + "");
}
}
else if (s.Contains("subjects") || s.Contains("subject"))
{
ans = "OK";
String s2, k1, k2, k3, k4, rep1 = "s";
int count;
SqlDataAdapter da3 = new SqlDataAdapter("Select * from Subjects where keywords = '2'", con);
DataSet ds3 = new DataSet();
da3.Fill(ds3);
count = Convert.ToInt32(ds3.Tables[0].Rows.Count);
for (int i = 0; i < count; i++)
{
k1 = Convert.ToString(ds3.Tables[0].Rows[i][2]).ToLower();
k2 = Convert.ToString(ds3.Tables[0].Rows[i][3]).ToLower();
k3 = Convert.ToString(ds3.Tables[0].Rows[i][4]).ToLower();
k4 = Convert.ToString(ds3.Tables[0].Rows[i][5]).ToLower();
// k5 = Convert.ToString(ds3.Tables[0].Rows[i][5]).ToLower();
if (s.Contains(k1) && (s.Contains(k2) || s.Contains(k4)) && s.Contains(k3))
{
rep1 = Convert.ToString(ds3.Tables[0].Rows[i][9]);
rep = rep1;
string del = "delete from Answer";
SqlCommand dcmd = new SqlCommand(del, con);
con.Open();
dcmd.ExecuteNonQuery();
con.Close();
SqlCommand cmd1 = new SqlCommand("Insert into Answer(Question,Answer) values('" + s + "','" + rep + "')", con);
con.Open();
cmd1.ExecuteNonQuery();
con.Close();
Response.Redirect("bot.aspx?reply=" + rep + "");
}
}
if (rep1 == "s")
{
da3 = new SqlDataAdapter("Select * from Subjects where keywords = '2' order by newid()", con);
ds3 = new DataSet();
da3.Fill(ds3);
rep = Convert.ToString(ds3.Tables[0].Rows[0][9]);
string del = "delete from Answer";
SqlCommand dcmd = new SqlCommand(del, con);
con.Open();
dcmd.ExecuteNonQuery();
con.Close();
SqlCommand cmd1 = new SqlCommand("Insert into Answer(Question,Answer) values('" + s + "','" + rep + "')", con);
con.Open();
cmd1.ExecuteNonQuery();
con.Close();
Response.Redirect("bot.aspx?reply=" + rep + "");
}
}
else if (s.Contains("cabin"))
{
ans = "OK";
String s2, k1, k2, rep1 = "s";
int count;
SqlDataAdapter da3 = new SqlDataAdapter("Select * from Cabin where keywords = '2'", con);
DataSet ds3 = new DataSet();
da3.Fill(ds3);
count = Convert.ToInt32(ds3.Tables[0].Rows.Count);
for (int i = 0; i < count; i++)
{
k1 = Convert.ToString(ds3.Tables[0].Rows[i][1]).ToLower();
k2 = Convert.ToString(ds3.Tables[0].Rows[i][2]).ToLower();
if (s.Contains(k1) && s.Contains(k2))
{
rep1 = Convert.ToString(ds3.Tables[0].Rows[i][6]);
rep = rep1;
string del = "delete from Answer";
SqlCommand dcmd = new SqlCommand(del, con);
con.Open();
dcmd.ExecuteNonQuery();
con.Close();
SqlCommand cmd1 = new SqlCommand("Insert into Answer(Question,Answer) values('" + s + "','" + rep + "')", con);
con.Open();
cmd1.ExecuteNonQuery();
con.Close();
Response.Redirect("bot.aspx?reply=" + rep + "");
}
}
if (rep1 == "s")
{
da3 = new SqlDataAdapter("Select * from Cabin where keywords = '2' order by newid()", con);
ds3 = new DataSet();
da3.Fill(ds3);
rep = Convert.ToString(ds3.Tables[0].Rows[0][6]);
string del = "delete from Answer";
SqlCommand dcmd = new SqlCommand(del, con);
con.Open();
dcmd.ExecuteNonQuery();
con.Close();
SqlCommand cmd1 = new SqlCommand("Insert into Answer(Question,Answer) values('" + s + "','" + rep + "')", con);
con.Open();
cmd1.ExecuteNonQuery();
con.Close();
Response.Redirect("bot.aspx?reply=" + rep + "");
}
}
else if (s.Contains("contact") || s.Contains("mobile"))
{
ans = "OK";
String s2, k1, k3, k4, k5, k6, rep1 = "s";
int count;
SqlDataAdapter da3 = new SqlDataAdapter("Select * from Contact where keywords = '3'", con);
DataSet ds3 = new DataSet();
da3.Fill(ds3);
count = Convert.ToInt32(ds3.Tables[0].Rows.Count);
for (int i = 0; i < count; i++)
{
k1 = Convert.ToString(ds3.Tables[0].Rows[i][1]).ToLower();
// k2 = Convert.ToString(ds3.Tables[0].Rows[i][2]).ToLower();
k3 = Convert.ToString(ds3.Tables[0].Rows[i][3]).ToLower();
k4 = Convert.ToString(ds3.Tables[0].Rows[i][4]).ToLower();
k5 = Convert.ToString(ds3.Tables[0].Rows[i][5]).ToLower();
// k6 = Convert.ToString(ds3.Tables[0].Rows[i][6]).ToLower();
// k7 = Convert.ToString(ds3.Tables[0].Rows[i][7]).ToLower();
if (s.Contains(k1) && s.Contains(k3) && (s.Contains(k4) || s.Contains(k5)))
{
rep1 = Convert.ToString(ds3.Tables[0].Rows[i][9]);
rep = rep1;
string del = "delete from Answer";
SqlCommand dcmd = new SqlCommand(del, con);
con.Open();
dcmd.ExecuteNonQuery();
con.Close();
SqlCommand cmd1 = new SqlCommand("Insert into Answer(Question,Answer) values('" + s + "','" + rep + "')", con);
con.Open();
cmd1.ExecuteNonQuery();
con.Close();
Response.Redirect("bot.aspx?reply=" + rep + "");
}
}
if (rep1 == "s")
{
da3 = new SqlDataAdapter("Select * from Contact where keywords = '3' order by newid()", con);
ds3 = new DataSet();
da3.Fill(ds3);
rep = Convert.ToString(ds3.Tables[0].Rows[0][9]);
string del = "delete from Answer";
SqlCommand dcmd = new SqlCommand(del, con);
con.Open();
dcmd.ExecuteNonQuery();
con.Close();
SqlCommand cmd1 = new SqlCommand("Insert into Answer(Question,Answer) values('" + s + "','" + rep + "')", con);
con.Open();
cmd1.ExecuteNonQuery();
con.Close();
Response.Redirect("bot.aspx?reply=" + rep + "");
}
}
else if (s.Contains("specialization") || s.Contains("specialization") || s.Contains("specialisation"))
{
ans = "OK";
String s2, k1, k2, rep1 = "s";
int count;
SqlDataAdapter da3 = new SqlDataAdapter("Select * from specialization where keywords = '2'", con);
DataSet ds3 = new DataSet();
da3.Fill(ds3);
count = Convert.ToInt32(ds3.Tables[0].Rows.Count);
for (int i = 0; i < count; i++)
{
k1 = Convert.ToString(ds3.Tables[0].Rows[i][1]).ToLower();
k2 = Convert.ToString(ds3.Tables[0].Rows[i][2]).ToLower();
if (s.Contains(k1) && s.Contains(k2))
{
rep1 = Convert.ToString(ds3.Tables[0].Rows[i][6]);
rep = rep1;
string del = "delete from Answer";
SqlCommand dcmd = new SqlCommand(del, con);
con.Open();
dcmd.ExecuteNonQuery();
con.Close();
SqlCommand cmd1 = new SqlCommand("Insert into Answer(Question,Answer) values('" + s + "','" + rep + "')", con);
con.Open();
cmd1.ExecuteNonQuery();
con.Close();
Response.Redirect("bot.aspx?reply=" + rep + "");
}
}
if (rep1 == "s")
{
da3 = new SqlDataAdapter("Select * from specialization where keywords = '2' order by newid()", con);
ds3 = new DataSet();
da3.Fill(ds3);
rep = Convert.ToString(ds3.Tables[0].Rows[0][6]);
string del = "delete from Answer";
SqlCommand dcmd = new SqlCommand(del, con);
con.Open();
dcmd.ExecuteNonQuery();
con.Close();
SqlCommand cmd1 = new SqlCommand("Insert into Answer(Question,Answer) values('" + s + "','" + rep + "')", con);
con.Open();
cmd1.ExecuteNonQuery();
con.Close();
Response.Redirect("bot.aspx?reply=" + rep + "");
}
}
else if (s.Contains("faculties"))
{
ans = "OK";
String s2, k1, k2, k3, rep1 = "s";
int count;
SqlDataAdapter da3 = new SqlDataAdapter("Select * from faculaties where keywords = '3'", con);
DataSet ds3 = new DataSet();
da3.Fill(ds3);
count = Convert.ToInt32(ds3.Tables[0].Rows.Count);
for (int i = 0; i < count; i++)
{
k1 = Convert.ToString(ds3.Tables[0].Rows[i][1]).ToLower();
k2 = Convert.ToString(ds3.Tables[0].Rows[i][2]).ToLower();
k3 = Convert.ToString(ds3.Tables[0].Rows[i][3]).ToLower();
if (s.Contains(k1) && s.Contains(k2) && s.Contains(k3))
{
rep1 = Convert.ToString(ds3.Tables[0].Rows[i][6]);
rep = rep1;
string del = "delete from Answer";
SqlCommand dcmd = new SqlCommand(del, con);
con.Open();
dcmd.ExecuteNonQuery();
con.Close();
SqlCommand cmd1 = new SqlCommand("Insert into Answer(Question,Answer) values('" + s + "','" + rep + "')", con);
con.Open();
cmd1.ExecuteNonQuery();
con.Close();
Response.Redirect("bot.aspx?reply=" + rep + "");
}
}
if (rep1 == "s")
{
da3 = new SqlDataAdapter("Select * from faculaties where keywords = '3' order by newid()", con);
ds3 = new DataSet();
da3.Fill(ds3);
rep = Convert.ToString(ds3.Tables[0].Rows[0][6]);
string del = "delete from Answer";
SqlCommand dcmd = new SqlCommand(del, con);
con.Open();
dcmd.ExecuteNonQuery();
con.Close();
SqlCommand cmd1 = new SqlCommand("Insert into Answer(Question,Answer) values('" + s + "','" + rep + "')", con);
con.Open();
cmd1.ExecuteNonQuery();
con.Close();
Response.Redirect("bot.aspx?reply=" + rep + "");
}
}
else if (s.Contains("classroom"))
{
ans = "OK";
String s2, k1, k2, rep1 = "s";
int count;
SqlDataAdapter da3 = new SqlDataAdapter("Select * from classroom where keywords = '2'", con);
DataSet ds3 = new DataSet();
da3.Fill(ds3);
count = Convert.ToInt32(ds3.Tables[0].Rows.Count);
for (int i = 0; i < count; i++)
{
k1 = Convert.ToString(ds3.Tables[0].Rows[i][1]).ToLower();
k2 = Convert.ToString(ds3.Tables[0].Rows[i][2]).ToLower();
if (s.Contains(k1) && s.Contains(k2))
{
rep1 = Convert.ToString(ds3.Tables[0].Rows[i][6]);
rep = rep1;
string del = "delete from Answer";
SqlCommand dcmd = new SqlCommand(del, con);
con.Open();
dcmd.ExecuteNonQuery();
con.Close();
SqlCommand cmd1 = new SqlCommand("Insert into Answer(Question,Answer) values('" + s + "','" + rep + "')", con);
con.Open();
cmd1.ExecuteNonQuery();
con.Close();
Response.Redirect("bot.aspx?reply=" + rep + "");
}
}
if (rep1 == "s")
{
da3 = new SqlDataAdapter("Select * from classroom where keywords = '2' order by newid()", con);
ds3 = new DataSet();
da3.Fill(ds3);
rep = Convert.ToString(ds3.Tables[0].Rows[0][6]);
string del = "delete from Answer";
SqlCommand dcmd = new SqlCommand(del, con);
con.Open();
dcmd.ExecuteNonQuery();
con.Close();
SqlCommand cmd1 = new SqlCommand("Insert into Answer(Question,Answer) values('" + s + "','" + rep + "')", con);
con.Open();
cmd1.ExecuteNonQuery();
con.Close();
Response.Redirect("bot.aspx?reply=" + rep + "");
}
}
else if (s.Contains("how"))
{
ans = "OK";
String s2, query1, query2, query3, rep1 = "s";
int count;
SqlDataAdapter da3 = new SqlDataAdapter("Select * from doublereply where keywords = '3'", con);
DataSet ds3 = new DataSet();
da3.Fill(ds3);
count = Convert.ToInt32(ds3.Tables[0].Rows.Count);
for (int i = 0; i < count; i++)
{
query1 = Convert.ToString(ds3.Tables[0].Rows[i][1]).ToLower();
query2 = Convert.ToString(ds3.Tables[0].Rows[i][2]).ToLower();
query3 = Convert.ToString(ds3.Tables[0].Rows[i][3]).ToLower();
if (s.Contains(query1) && s.Contains(query2) && s.Contains(query3))
{
rep1 = Convert.ToString(ds3.Tables[0].Rows[i][6]);
rep = rep1;
string del = "delete from Answer";
SqlCommand dcmd = new SqlCommand(del, con);
con.Open();
dcmd.ExecuteNonQuery();
con.Close();
SqlCommand cmd1 = new SqlCommand("Insert into Answer(Question,Answer) values('" + s + "','" + rep + "')", con);
con.Open();
cmd1.ExecuteNonQuery();
con.Close();
Response.Redirect("bot.aspx?reply=" + rep + "");
}
}
if (rep1 == "s")
{
da3 = new SqlDataAdapter("Select * from doublereply where keywords = '3' order by newid()", con);
ds3 = new DataSet();
da3.Fill(ds3);
rep = Convert.ToString(ds3.Tables[0].Rows[0][6]);
string del = "delete from Answer";
SqlCommand dcmd = new SqlCommand(del, con);
con.Open();
dcmd.ExecuteNonQuery();
con.Close();
SqlCommand cmd1 = new SqlCommand("Insert into Answer(Question,Answer) values('" + s + "','" + rep + "')", con);
con.Open();
cmd1.ExecuteNonQuery();
con.Close();
Response.Redirect("bot.aspx?reply=" + rep + "");
}
}
else if (s.Contains("what"))
{
ans = "OK";
String s2, query1, query2, query3, rep1 = "s";
int count;
SqlDataAdapter da3 = new SqlDataAdapter("Select * from whatdoublereply where keywords = '3'", con);
DataSet ds3 = new DataSet();
da3.Fill(ds3);
count = Convert.ToInt32(ds3.Tables[0].Rows.Count);
for (int i = 0; i < count; i++)
{
query1 = Convert.ToString(ds3.Tables[0].Rows[i][1]).ToLower();
query2 = Convert.ToString(ds3.Tables[0].Rows[i][2]).ToLower();
query3 = Convert.ToString(ds3.Tables[0].Rows[i][3]).ToLower();
if (s.Contains(query1) && s.Contains(query2) && s.Contains(query3))
{
rep1 = Convert.ToString(ds3.Tables[0].Rows[i][6]);
rep = rep1;
string del = "delete from Answer";
SqlCommand dcmd = new SqlCommand(del, con);
con.Open();
dcmd.ExecuteNonQuery();
con.Close();
SqlCommand cmd1 = new SqlCommand("Insert into Answer(Question,Answer) values('" + s + "','" + rep + "')", con);
con.Open();
cmd1.ExecuteNonQuery();
con.Close();
Response.Redirect("bot.aspx?reply=" + rep + "");
}
}
if (rep1 == "s")
{
da3 = new SqlDataAdapter("Select * from whatdoublereply where keywords = '3' order by newid()", con);
ds3 = new DataSet();
da3.Fill(ds3);
rep = Convert.ToString(ds3.Tables[0].Rows[0][6]);
string del = "delete from Answer";
SqlCommand dcmd = new SqlCommand(del, con);
con.Open();
dcmd.ExecuteNonQuery();
con.Close();
SqlCommand cmd1 = new SqlCommand("Insert into Answer(Question,Answer) values('" + s + "','" + rep + "')", con);
con.Open();
cmd1.ExecuteNonQuery();
con.Close();
Response.Redirect("bot.aspx?reply=" + rep + "");
}
}
else if (s.Contains("where"))
{
ans = "OK";
String s2, query1, query2, query3, rep1 = "s";
int count;
SqlDataAdapter da3 = new SqlDataAdapter("Select * from wheredoublereply where keywords = '3'", con);
DataSet ds3 = new DataSet();
da3.Fill(ds3);
count = Convert.ToInt32(ds3.Tables[0].Rows.Count);
for (int i = 0; i < count; i++)
{
query1 = Convert.ToString(ds3.Tables[0].Rows[i][1]).ToLower();
query2 = Convert.ToString(ds3.Tables[0].Rows[i][2]).ToLower();
query3 = Convert.ToString(ds3.Tables[0].Rows[i][3]).ToLower();
if (s.Contains(query1) && s.Contains(query2) && s.Contains(query3))
{
rep1 = Convert.ToString(ds3.Tables[0].Rows[i][6]);
rep = rep1;
string del = "delete from Answer";
SqlCommand dcmd = new SqlCommand(del, con);
con.Open();
dcmd.ExecuteNonQuery();
con.Close();
SqlCommand cmd1 = new SqlCommand("Insert into Answer(Question,Answer) values('" + s + "','" + rep + "')", con);
con.Open();
cmd1.ExecuteNonQuery();
con.Close();
Response.Redirect("bot.aspx?reply=" + rep + "");
}
}
if (rep1 == "s")
{
da3 = new SqlDataAdapter("Select * from wheredoublereply where keywords = '3' order by newid()", con);
ds3 = new DataSet();
da3.Fill(ds3);
rep = Convert.ToString(ds3.Tables[0].Rows[0][6]);
string del = "delete from Answer";
SqlCommand dcmd = new SqlCommand(del, con);
con.Open();
dcmd.ExecuteNonQuery();
con.Close();
SqlCommand cmd1 = new SqlCommand("Insert into Answer(Question,Answer) values('" + s + "','" + rep + "')", con);
con.Open();
cmd1.ExecuteNonQuery();
con.Close();
Response.Redirect("bot.aspx?reply=" + rep + "");
}
}
else if (s.Contains("who"))
{
ans = "OK";
String s2, query1, query2, query3, rep1 = "s";
int count;
SqlDataAdapter da3 = new SqlDataAdapter("Select * from whodoublereply where keywords = '3'", con);
DataSet ds3 = new DataSet();
da3.Fill(ds3);
count = Convert.ToInt32(ds3.Tables[0].Rows.Count);
for (int i = 0; i < count; i++)
{
query1 = Convert.ToString(ds3.Tables[0].Rows[i][1]).ToLower();
query2 = Convert.ToString(ds3.Tables[0].Rows[i][2]).ToLower();
query3 = Convert.ToString(ds3.Tables[0].Rows[i][3]).ToLower();
if (s.Contains(query1) && s.Contains(query2) && s.Contains(query3))
{
rep1 = Convert.ToString(ds3.Tables[0].Rows[i][6]);
rep = rep1;
string del = "delete from Answer";
SqlCommand dcmd = new SqlCommand(del, con);
con.Open();
dcmd.ExecuteNonQuery();
con.Close();
SqlCommand cmd1 = new SqlCommand("Insert into Answer(Question,Answer) values('" + s + "','" + rep + "')", con);
con.Open();
cmd1.ExecuteNonQuery();
con.Close();
Response.Redirect("bot.aspx?reply=" + rep + "");
}
}
if (rep1 == "s")
{
da3 = new SqlDataAdapter("Select * from whodoublereply where keywords = '3' order by newid()", con);
ds3 = new DataSet();
da3.Fill(ds3);
rep = Convert.ToString(ds3.Tables[0].Rows[0][6]);
string del = "delete from Answer";
SqlCommand dcmd = new SqlCommand(del, con);
con.Open();
dcmd.ExecuteNonQuery();
con.Close();
SqlCommand cmd1 = new SqlCommand("Insert into Answer(Question,Answer) values('" + s + "','" + rep + "')", con);
con.Open();
cmd1.ExecuteNonQuery();
con.Close();
Response.Redirect("bot.aspx?reply=" + rep + "");
}
}
else if (ans != "OK")
{
rep = Convert.ToString("No related Answer Found");
string del = "delete from Answer";
SqlCommand dcmd = new SqlCommand(del, con);
con.Open();
dcmd.ExecuteNonQuery();
con.Close();
SqlCommand cmd1 = new SqlCommand("Insert into Answer(Question,Answer) values('" + s + "','" + rep + "')", con);
con.Open();
cmd1.ExecuteNonQuery();
con.Close();
Response.Redirect("bot.aspx?reply=" + rep + "");
}
}
else
{
Response.Redirect("bot.aspx?reply=none");
}
}
public DataSet fetch(string query)
{
using (this.sqlconnection = new SqlConnection(this.connstring))
{
this.sqlconnection.Open();
//SqlTransaction trans = this.conn.BeginTransaction();
try
{
SqlDataAdapter da = new SqlDataAdapter(query, this.conn);
DataSet ds = new DataSet();
da.Fill(ds);
//trans.Commit();
conn.Close();
return ds;
}
catch (Exception ex)
{
try
{
if (this.sqlconnection.State != ConnectionState.Open)
throw new Exception("Connection Is Not Open" + ex.Message);
//else
//trans.Rollback();
}
catch (Exception ex1)
{
throw new Exception("Connection Is Not Open" + ex1.Message);
}
return (DataSet)null;
}
finally
{
if (this.sqlconnection.State != ConnectionState.Open)
this.conn.Close();
}
}
}
/// <summary>
/// 尋找任務
/// </summary>
/// <returns></returns>
private DataTable RefreshgvMain()
{
string search = "";
if (!string.IsNullOrEmpty(txtDOC_NBR.Text))
{
search += " AND DOC_NBR = @DOC_NBR"; // 表單單號
}
if (dateStart.SelectedDate != null)
{
search += string.Format(" AND CONVERT(datetime,{0}) >= @START2", FROM_TYPE == "LEAVE" ? "STARTTIME" : "OT_START"); // 時間(起)
}
if (dateEnd.SelectedDate != null)
{
search += string.Format(" AND CONVERT(datetime,{0}) <= @END2", FROM_TYPE == "LEAVE" ? "ENDTIME" : "OT_END"); // 時間(迄)
}
DataTable dt = new DataTable();
if (FROM_TYPE == "LEAVE") // 請假單
{
using (SqlDataAdapter sda = new SqlDataAdapter(@"
-- SELECT '===請選擇===' AS 'SHOW', '' AS 'DOC_NBR'
-- UNION ALL
SELECT DOC_NBR AS 'SHOW', DOC_NBR, STARTTIME, ENDTIME
FROM Z_SCSHR_LEAVE
WHERE (CANCEL_DOC_NBR = '' OR CANCEL_DOC_NBR IS NULL)
AND TASK_STATUS = 2
AND TASK_RESULT = 0
AND APPLICANTGUID = @APPLICANTGUID
" + search, ConnectionString))
using (DataSet ds = new DataSet())
{
sda.SelectCommand.Parameters.AddWithValue("@DOC_NBR", txtDOC_NBR.Text);
sda.SelectCommand.Parameters.AddWithValue("@APPLICANTGUID", USER_GUID);
if (dateStart.SelectedDate != null)
{
sda.SelectCommand.Parameters.AddWithValue("@START2", ((DateTime)dateStart.SelectedDate).ToString("yyyy-MM-dd HH:mm"));
}
if (dateEnd.SelectedDate != null)
{
sda.SelectCommand.Parameters.AddWithValue("@END2", ((DateTime)dateEnd.SelectedDate).ToString("yyyy-MM-dd HH:mm"));
}
try
{
sda.Fill(ds);
dt = ds.Tables[0];
if (dt.Rows.Count > 0)
{
dt.Columns.Add(new DataColumn("START_TIME", typeof(string)));
dt.Columns.Add(new DataColumn("END_TIME", typeof(string)));
foreach (DataRow dr in dt.Rows)
{
dr["START_TIME"] = dr["STARTTIME"];
dr["END_TIME"] = dr["ENDTIME"];
}
}
}
catch (Exception ex)
{
KYTUtilLibs.KYTDebugLog.Log(KYTLog.DebugLog.LogLevel.Error, "SearchForm::RefreshgvMain()::L請假單::錯誤:{0}", ex.Message);
}
}
}
else if (FROM_TYPE == "OVER_TIME") // 加班單
{
using (SqlDataAdapter sda = new SqlDataAdapter(@"
-- SELECT '===請選擇===' AS 'SHOW', '' AS 'DOC_NBR'
-- UNION ALL
SELECT DOC_NBR AS 'SHOW', DOC_NBR, OT_START, OT_END
FROM Z_SCSHR_OVERTIME
WHERE (CANCEL_DOC_NBR = '' OR CANCEL_DOC_NBR IS NULL)
AND TASK_STATUS = 2
AND TASK_RESULT = 0
AND APPLICANTGUID = @APPLICANTGUID
AND ((OT_START BETWEEN @START AND @END)
OR (OT_END BETWEEN @START AND @END))
-- AND ((OT_START BETWEEN DATEADD(mm, DATEDIFF(mm,0,GETDATE()), 0) -- 加班時間(起)在該月(月底23:59:59.997)
-- AND DATEADD(ms, -2, DATEADD(mm, DATEDIFF(m, 0, GETDATE()) + 1, 0)))
-- OR (OT_END BETWEEN DATEADD(mm, DATEDIFF(mm,0,GETDATE()), 0) -- 或加班時間(迄)在該月(月底23:59:59.997)
-- AND DATEADD(ms, -2, DATEADD(mm, DATEDIFF(m, 0, GETDATE()) + 1, 0))) )
" + search, ConnectionString))
using (DataSet ds = new DataSet())
{
sda.SelectCommand.Parameters.AddWithValue("@DOC_NBR", txtDOC_NBR.Text);
sda.SelectCommand.Parameters.AddWithValue("@APPLICANTGUID", USER_GUID);
sda.SelectCommand.Parameters.AddWithValue("@START", DateTime.Now.AddMonths(-1));
sda.SelectCommand.Parameters.AddWithValue("@END", DateTime.Now);
if (dateStart.SelectedDate != null)
{
sda.SelectCommand.Parameters.AddWithValue("@START2", ((DateTime)dateStart.SelectedDate).ToString("yyyy-MM-dd HH:mm"));
}
if (dateEnd.SelectedDate != null)
{
sda.SelectCommand.Parameters.AddWithValue("@END2", ((DateTime)dateEnd.SelectedDate).ToString("yyyy-MM-dd HH:mm"));
}
try
{
sda.Fill(ds);
dt = ds.Tables[0];
if (dt.Rows.Count > 0)
{
dt.Columns.Add(new DataColumn("START_TIME", typeof(string)));
dt.Columns.Add(new DataColumn("END_TIME", typeof(string)));
foreach (DataRow dr in dt.Rows)
{
dr["START_TIME"] = dr["OT_START"];
dr["END_TIME"] = dr["OT_END"];
}
}
}
catch (Exception ex)
{
KYTUtilLibs.KYTDebugLog.Log(KYTLog.DebugLog.LogLevel.Error, "SearchForm::RefreshgvMain()::O加班單::錯誤:{0}", ex.Message);
}
}
}
ViewState[gvMain.ID] = dt;
return(dt);
}
//返回客户类别编码
public static string GetCustTypeCode(string CustTypeName)
{
System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection();
conn.ConnectionString = ConnStr;
try
{
SqlCommand selectCMD = new SqlCommand("SELECT typecode FROM custtype where typename='" + CustTypeName + "'", conn);
selectCMD.CommandTimeout = 30;
SqlDataAdapter dbDA = new SqlDataAdapter();
dbDA.SelectCommand = selectCMD;
conn.Open();
DataSet dbDS = new DataSet();
dbDA.Fill(dbDS, "t");
return dbDS.Tables[0].Rows[0][0].ToString().Trim();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
conn.Close();
}
return "";
}
protected void BindSchemeChart()
{
DataTable dsGoalChartData = new DataTable();
StringBuilder strGoalSchChart = new StringBuilder();
string xCode = Session["userSessionID"].ToString();
//try
//{
strGoalSchChart.Append(@"<script type='text/javascript'>
AmCharts.ready(function () {
generateChartData();
createStockChart();
});
var chartData1 = [];
var chartData2 = [];
var chartData3 = [];
function generateChartData() {
");
//Select No. of Months
int xMonth = 0;
string xSIPDt = "";
string xProjDate = "";
double xPrin = 0;
//double xCumAmount = 0;
double sipamt = Convert.ToDouble(ViewState["xSIPAmt"]);
double actamt = Convert.ToDouble(ViewState["xActInv"]);
double sUnit = 0;
double aUnit = 0;
double sCumUnit = 0;
double aCumUnit = 0;
double sProjectedAmt = 0;
double aProjectedAmt = 0;
double SuggestedAmt = 0;
string xSIPDate = "";
string dd = "";
string mm = "";
string yy = "";
// Query for selecting SIP start Month and No. of month for SIP
SqlDataAdapter NoMonDA = new SqlDataAdapter(" " +
" select SIPStartDate=Convert(varchar(10),EntryDate,120), NoMonth=datediff(M,EntryDate,Startdate) from fp_GoalDetails " +
" where GroupCode=(Select Code from fp_prospects where userid='" + ViewState["userid"] + "') and Code=" + ViewState["xGoalCode"] + " " +
" ", Con);
DataSet NoMonDS = new DataSet();
Con.Open();
NoMonDA.Fill(NoMonDS);
Con.Close();
if (NoMonDS.Tables[0].Rows.Count > 0)
{
xMonth = Convert.ToInt32(NoMonDS.Tables[0].Rows[0]["NoMonth"]);
xSIPDt = NoMonDS.Tables[0].Rows[0]["SIPStartDate"].ToString();
}
CultureInfo culture = new CultureInfo("en-GB");
DateTime SIPDate;
SIPDate = Convert.ToDateTime(xSIPDt, culture);
for (int j = 1; j <= xMonth; j++)
{
double sNAV = 0;
string xSIPStartDt = SIPDate.ToString();
dd = SIPDate.Day.ToString().PadLeft(2, '0');
mm = SIPDate.Month.ToString().PadLeft(2, '0');
yy = SIPDate.Year.ToString();
xSIPDate = yy + "-" + mm + "-" + dd;
// Query for selecting NAV of SIP start Month
SqlDataAdapter NavDA = new SqlDataAdapter(" " +
" select NAV, SIPDate=Convert(varchar(10),NAVDate,120) from navexlc where exlcode=" + ViewState["xExlCode"] + " " +
" and NavDate=(select Min(NAVDate) from navexlc where exlcode=" + ViewState["xExlCode"] + " and NavDate >= '" + xSIPDate + "') " +
" ", Con);
DataSet NavDS = new DataSet();
Con.Open();
NavDA.Fill(NavDS);
Con.Close();
if (NavDS.Tables[0].Rows.Count > 0)
{
sNAV = Convert.ToDouble(NavDS.Tables[0].Rows[0]["NAV"]);
xProjDate = NavDS.Tables[0].Rows[0]["SIPDate"].ToString();
// getting unit for Suggested SIP Amount based on every months SIP
sUnit = (sipamt / sNAV);
// getting unit for Actual Investment Amount based on every months SIP
aUnit = (actamt / sNAV);
if (j == 1)
{
sCumUnit = sUnit;
aCumUnit = aUnit;
sProjectedAmt = sCumUnit * sNAV;
aProjectedAmt = aCumUnit * sNAV;
}
else
{
sCumUnit = sCumUnit + sUnit;
aCumUnit = aCumUnit + aUnit;
sProjectedAmt = sCumUnit * sNAV;
aProjectedAmt = aCumUnit * sNAV;
}
strGoalSchChart.Append("chartData2.push({ date: new Date(" + yy + "," + mm + "," + dd + "), value: " + sProjectedAmt + " });");
strGoalSchChart.Append("chartData3.push({ date: new Date(" + yy + "," + mm + "," + dd + "), value: " + aProjectedAmt + " });");
}
// Calculation for Suggested Amount with Risk
if (j == 1)
{
xPrin = sipamt;
SuggestedAmt = sipamt;
}
else
{
xPrin = SuggestedAmt + sipamt;
SuggestedAmt = Math.Round(xPrin * Math.Pow((1 + Convert.ToDouble(xRiskPer) * 1.0000 / 100), 1), 0);
}
strGoalSchChart.Append("chartData1.push({ date: new Date(" + yy + "," + mm + "," + dd + "), value: " + SuggestedAmt + " });");
SIPDate = SIPDate.AddMonths(j);
}
strGoalSchChart.Append(@"}
function createStockChart() {
var chartScheme = new AmCharts.AmStockChart();
chartScheme.pathToImages = '../ChartNew/amcharts/images/';
// DATASETS //////////////////////////////////////////
// create data sets first
var dataSet1 = new AmCharts.DataSet();
dataSet1.title = 'Inv. on suggested amt';
dataSet1.fieldMappings = [{
fromField: 'value',
toField: 'value'
}, {
fromField: 'volume',
toField: 'volume'
}];
dataSet1.dataProvider = chartData1;
dataSet1.categoryField = 'date';
dataSet1.lineThickness = 1;
var dataSet2 = new AmCharts.DataSet();
dataSet2.title = 'Suggested Amt. Performance';
dataSet2.fieldMappings = [{
fromField: 'value',
toField: 'value'
}, {
fromField: 'volume',
toField: 'volume'
}];
dataSet2.dataProvider = chartData2;
dataSet2.categoryField = 'date';
dataSet2.lineThickness = 1;
var dataSet3 = new AmCharts.DataSet();
dataSet3.title = 'Actual Amt. Performance';
dataSet3.fieldMappings = [{
fromField: 'value',
toField: 'value'
}, {
fromField: 'volume',
toField: 'volume'
}];
dataSet3.dataProvider = chartData3;
dataSet3.categoryField = 'date';
dataSet3.lineThickness = 1;
// set data sets to the chart
chartScheme.dataSets = [dataSet1, dataSet2, dataSet3];
// PANELS ///////////////////////////////////////////
// first stock panel
var stockPanel1 = new AmCharts.StockPanel();
stockPanel1.showCategoryAxis = false;
stockPanel1.title = 'Value';
stockPanel1.percentHeight = 70;
// graph of first stock panel
var graph1 = new AmCharts.StockGraph();
graph1.valueField = 'value';
graph1.comparable = true;
graph1.compareField = 'value';
graph1.lineThickness = 1;
stockPanel1.addStockGraph(graph1);
// create stock legend
stockPanel1.stockLegend = new AmCharts.StockLegend();
// second stock panel
var stockPanel2 = new AmCharts.StockPanel();
stockPanel2.percentHeight = 10;
stockPanel2.backgroundColor = '#ff0000';
var graph2 = new AmCharts.StockGraph();
//graph2.valueField = 'volume';
//graph2.type = 'column';
//graph2.showBalloon = false;
graph2.fillAlphas = 1;
//stockPanel2.addStockGraph(graph2);
stockPanel2.stockLegend = new AmCharts.StockLegend();
// set panels to the chart
chartScheme.panels = [stockPanel1, stockPanel2];
// OTHER SETTINGS ////////////////////////////////////
sbsettings = new AmCharts.ChartScrollbarSettings();
sbsettings.graph = graph1;
chartScheme.chartScrollbarSettings = sbsettings;
// PERIOD SELECTOR ///////////////////////////////////
var periodSelector = new AmCharts.PeriodSelector();
periodSelector.position = 'left';
periodSelector.inputFieldsEnabled = false;
periodSelector.periods = [{
period: 'DD',
count: 10,
label: '10 days'
}, {
period: 'MM',
count: 1,
label: '1 month'
}, {
period: 'YYYY',
count: 1,
label: '1 year'
}, {
period: 'YTD',
label: 'YTD'
}, {
period: 'MAX',
selected: true,
label: 'MAX'
}];
chartScheme.periodSelector = periodSelector;
// DATA SET SELECTOR
var dataSetSelector = new AmCharts.DataSetSelector();
dataSetSelector.position = 'left';
dataSetSelector.width = 200;
chartScheme.dataSetSelector = dataSetSelector;
chartScheme.write('retirementChartDiv');
}
</script> " );
ltGoalSchChart.Text = strGoalSchChart.ToString();
//}
//catch
//{
//}
//finally
//{
dsGoalChartData.Dispose();
strGoalSchChart.Clear();
//}
}
private void button2_Click(object sender, EventArgs e)
{
String Querry;
if (this.richTextBox1.Text == "")
{
Querry = "select * from _User";
try
{
if (dataGridView1.Rows.Count == 0)
{
SqlCommand prt = new SqlCommand(Querry, Important.conex);
SqlDataAdapter da = new SqlDataAdapter(prt);
DataSet ds = new DataSet();
da.Fill(ds);
dataGridView1.DataSource = ds.Tables[0];
}
else
{
dataGridView1.DataSource = null;
dataGridView1.Refresh();
}
}
catch (Exception ex)
{
Important.message = ex.GetType().ToString();
DialogMessageP dial = new DialogMessageP();
dial.ShowDialog();
}
}
else
{
Querry = "select * from _User where " + this.richTextBox1.Text;
try
{
SqlCommand prt = new SqlCommand(Querry, Important.conex);
SqlDataAdapter da = new SqlDataAdapter(prt);
DataSet ds = new DataSet();
da.Fill(ds);
dataGridView1.DataSource = ds.Tables[0];
}
catch (Exception ex)
{
Important.message = ex.GetType().ToString();
DialogMessageP dial = new DialogMessageP();
dial.ShowDialog();
}
}
this.textBox1.Text = "";
this.textBox6.Text = "";
this.textBox2.Text = "";
this.textBox3.Text = "";
this.textBox4.Text = "";
this.textBox5.Text = "";
this.richTextBox1.Text = "";
}
private void comboBox_SelectionChanged(object sender, SelectionChangedEventArgs e)
{
da.SelectCommand.CommandText = "select * from StudentTable where StuNo='" + comboBox.SelectedItem.ToString() + "'";
ds.Clear();
da.Fill(ds, "StudentTable");
}
private void btn_trie_Click(object sender, EventArgs e)
{
DA = new SqlDataAdapter("Select * from clients where ville ='" + cbville.Text + "'", cnx);
DA.Fill(DS, "clients");
dataGridView1.DataSource = DS.Tables["clients"];
}
//填充操作员信息 LogOperatorID
public static void ReceiveOperatorInfo(string userid)
{
//根据LogOperator返回雇员信息
opinfo.OpID = userid;
System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection();
conn.ConnectionString = ConnStr;
try
{
SqlCommand selectCMD = new SqlCommand("SELECT * FROM Operator where OperatorID='" + userid + "'", conn);
selectCMD.CommandTimeout = 30;
SqlDataAdapter dbDA = new SqlDataAdapter();
dbDA.SelectCommand = selectCMD;
conn.Open();
DataSet dbDS = new DataSet();
dbDA.Fill(dbDS, "t");
//获得 EmployeeID->empno
string empno;
empno = dbDS.Tables[0].Rows[0]["EmpID"].ToString();
opinfo.EmpID = empno;
//根据EmpNo返回EmployeeName 和 Department
selectCMD.Dispose();
selectCMD.Connection = conn;
selectCMD.CommandText = "SELECT * FROM Employee where EmpID='" + empno + "'";
dbDA.SelectCommand = selectCMD;
dbDS.Clear();
dbDA.Fill(dbDS, "t");
opinfo.EmployeeName = dbDS.Tables[0].Rows[0]["name"].ToString();
//返回部门名称
string depno;
depno = dbDS.Tables[0].Rows[0]["depcode"].ToString();
selectCMD.Dispose();
selectCMD.Connection = conn;
selectCMD.CommandText = "SELECT * FROM Department where depcode='" + depno + "'";
dbDA.SelectCommand = selectCMD;
dbDS.Clear();
dbDA.Fill(dbDS, "t");
opinfo.Department = dbDS.Tables[0].Rows[0]["deptname"].ToString();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
conn.Close();
}
}
public string getchildes(int x, string UTID)
{
SqlConnection Conn = new SqlConnection(ConfigurationManager.ConnectionStrings["Con"].ConnectionString);
string SubString = "";
int k = 0;
string str = "select distinct FormID As id, FormTitle As name , PID As parentid , Path As path from VW_MENU where PID=" + x + " and UTID='" + UTID + "' order by FormID";
SqlDataAdapter da = new SqlDataAdapter(str, Conn);
DataTable dtA = new DataTable();
da.Fill(dtA);
int noOfChild = dtA.Rows.Count;
for (int i = 0; i < dtA.Rows.Count; i++)
{
if (dtA.Rows.Count == 0)
{
SubString = "";
}
else
{
string hasChild = getSubchildes(Convert.ToInt32(dtA.Rows[i]["id"].ToString()), UTID);
if (hasChild == "")
{
string ActiveChild = "";
string path = dtA.Rows[i]["path"].ToString();
path = path.ToLowerInvariant();
if (PageURL.IndexOf(path) != -1)
{
ActiveChild = " class=active";
}
SubString = SubString + "<li>";
SubString = SubString + "<a href='" + "../" + dtA.Rows[i]["path"].ToString() + "" + "'>";
SubString = SubString + "<span class='menu-text'>" + dtA.Rows[i]["name"].ToString() + "</span>";
// SubString = SubString + "<span class='menu-badge'><span class='badge vd_bg-yellow'>NEW</span></span>";
SubString = SubString + "</a>";
SubString = SubString + "</li>";
}
else
{
string ActiveParent = "";
if (hasChild.IndexOf("class=active") != -1)
{
ActiveParent = " active";
}
string TotalChild = hasChild;
TotalChild = TotalChild.Substring(0, TotalChild.IndexOf("`"));
hasChild = hasChild.Substring(hasChild.IndexOf("`") + 1);
//SubString = SubString + "<li><a><i class='fa fa-edit'></i>" + dtA.Rows[i]["name"].ToString() + "";
//SubString = SubString + "<span class='fa fa-chevron-down'></span></a>";
//SubString = SubString + "</a>";
//SubString = SubString + "<ul class='nav child_menu'>";
//SubString = SubString + hasChild;
//SubString = SubString + "</ul></li>";
SubString = SubString + "<li>";
SubString = SubString + "<a href='" + "../" + dtA.Rows[i]["path"].ToString() + "" + "'>";
SubString = SubString + "<span class='menu-text'>" + dtA.Rows[i]["name"].ToString() + "</span>";
//SubString = SubString + "<span class='menu-badge'><span class='badge vd_bg-yellow'>NEW</span></span>";
SubString = SubString + "</a>";
SubString = SubString + "</li>";
}
}
}
if (noOfChild > 0)
{
SubString = noOfChild + "`" + SubString;
}
return(SubString);
}
protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
{
if (Session["company_id"] != null)
{
company_id = Convert.ToInt32(Session["company_id"].ToString());
}
SqlConnection con1 = new SqlConnection(ConfigurationManager.AppSettings["connection"]);
SqlCommand CMD = new SqlCommand("select * from product_stock where Com_Id='" + DropDownList1.SelectedItem.Value + "' ORDER BY purchase_invoice asc", con1);
DataTable dt1 = new DataTable();
con1.Open();
SqlDataAdapter da1 = new SqlDataAdapter(CMD);
da1.Fill(dt1);
GridView1.DataSource = dt1;
GridView1.DataBind();
if (Session["company_id"] != null)
{
company_id = Convert.ToInt32(Session["company_id"].ToString());
}
SqlConnection con = new SqlConnection(ConfigurationManager.AppSettings["connection"]);
SqlCommand cmd = new SqlCommand("Select * from category where Com_Id='" + DropDownList1.SelectedItem.Value + "' ORDER BY category_id asc", con);
con.Open();
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
DropDownList3.DataSource = ds;
DropDownList3.DataTextField = "categoryname";
DropDownList3.DataValueField = "category_id";
DropDownList3.DataBind();
DropDownList3.Items.Insert(0, new ListItem("All", "0"));
con.Close();
if (Session["company_id"] != null)
{
company_id = Convert.ToInt32(Session["company_id"].ToString());
}
SqlConnection con2 = new SqlConnection(ConfigurationManager.AppSettings["connection"]);
SqlCommand cmd1 = new SqlCommand("Select * from product_stock where Com_Id='" + DropDownList1.SelectedItem.Value + "' ORDER BY no asc", con2);
con2.Open();
DataSet ds1 = new DataSet();
SqlDataAdapter da2 = new SqlDataAdapter(cmd1);
da2.Fill(ds1);
DropDownList2.DataSource = ds1;
DropDownList2.DataTextField = "barcode";
DropDownList2.DataValueField = "no";
DropDownList2.DataBind();
DropDownList2.Items.Insert(0, new ListItem("All", "0"));
con2.Close();
company_id1 = Convert.ToInt32(DropDownList1.SelectedItem.Value);
}
//检测当前客户是否允许签单
public static bool AllowSignPay(string customercode)
{
System.Data.SqlClient.SqlConnection conn1 = new System.Data.SqlClient.SqlConnection();
conn1.ConnectionString = ConnStr;
try
{
SqlCommand selectCMD1 = new SqlCommand();
selectCMD1.Connection = conn1;
selectCMD1.CommandText = "SELECT signed FROM customer where customercode='" + customercode + "'";
selectCMD1.CommandTimeout = 30;
SqlDataAdapter dbDA1 = new SqlDataAdapter();
dbDA1.SelectCommand = selectCMD1;
conn1.Open();
DataSet dbDS1 = new DataSet();
dbDA1.Fill(dbDS1, "t1");
if (dbDS1.Tables[0].Rows[0][0].ToString() == "1")
{
return true;
}
else
{
return false;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
return false;
}
finally
{
conn1.Close();
}
}
public DataTable SaveDriverApprovals(Approvals a)
{
//connect to database
SqlConnection conn = new SqlConnection();
DataTable dt = new DataTable();
LogTraceWriter traceWriter = new LogTraceWriter();
try
{
//connetionString="Data Source=ServerName;Initial Catalog=DatabaseName;User ID=UserName;Password=Password"
conn.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["btposdb"].ToString();
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "PSinsupddriverApprovals";
cmd.Connection = conn;
SqlParameter LocationId = new SqlParameter("@Change", SqlDbType.VarChar);
LocationId.Value = a.change;
cmd.Parameters.Add(LocationId);
SqlParameter parentid = new SqlParameter("@IsApproved", SqlDbType.Int);
parentid.Value = a.IsApproved;
cmd.Parameters.Add(parentid);
SqlParameter flag = new SqlParameter("@MobileNo", SqlDbType.VarChar);
flag.Value = a.MobileNo;
cmd.Parameters.Add(flag);
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt);
#region email opt
string eotp = dt.Rows[0]["Email"].ToString();
if (eotp != null)
{
try
{
MailMessage mail = new MailMessage();
string emailserver = System.Configuration.ConfigurationManager.AppSettings["emailserver"].ToString();
string username = System.Configuration.ConfigurationManager.AppSettings["username"].ToString();
string pwd = System.Configuration.ConfigurationManager.AppSettings["password"].ToString();
string fromaddress = System.Configuration.ConfigurationManager.AppSettings["fromaddress"].ToString();
string port = System.Configuration.ConfigurationManager.AppSettings["port"].ToString();
SmtpClient SmtpServer = new SmtpClient(emailserver);
mail.From = new MailAddress(fromaddress);
mail.To.Add(fromaddress);
mail.Subject = "Driver Approval";
mail.IsBodyHtml = true;
string verifcodeMail = @"<table>
<tr>
<td>
<h2>Thank you for registering with PaySmart APP</h2>
<table width=\""760\"" align=\""center\"">
<tbody style='background-color:#F0F8FF;'>
<tr>
<td style=\""font-family:'Zurich BT',Arial,Helvetica,sans-serif;font-size:15px;text-align:left;line-height:normal;background-color:#F0F8FF;\"" >
<div style='padding:10px;border:#0000FF solid 2px;'> <br /><br />
Driver has been Approved for:<h3>" + eotp + @" </h3>
If you didn't make this request, <a href='http://154.120.237.198:52800'>click here</a> to cancel.
<br/>
<br/>
Warm regards,<br>
PAYSMART Customer Service Team<br/><br />
</div>
</td>
</tr>
</tbody>
</table>
</td>
</tr>
</table>";
mail.Body = verifcodeMail;
//SmtpServer.Port = 465;
//SmtpServer.Port = 587;
SmtpServer.Port = Convert.ToInt32(port);
SmtpServer.UseDefaultCredentials = false;
SmtpServer.Credentials = new System.Net.NetworkCredential(username, pwd);
SmtpServer.EnableSsl = true;
//SmtpServer.TargetName = "STARTTLS/smtp.gmail.com";
SmtpServer.Send(mail);
}
catch (Exception ex)
{
traceWriter.Trace(Request, "0", TraceLevel.Error, "{0}", "Register driver Email otp failed...." + ex.Message);
throw ex;
}
}
//send mobile otp
// return dt;
#endregion email otp
}
catch (Exception ex)
{
if (conn != null && conn.State == ConnectionState.Open)
{
conn.Close();
}
throw ex;
}
return dt;
}
private void button4_Click(object sender, EventArgs e)
{
string Update;
string Select;
string Check;
try
{
Check = "select FullName from _User where " + this.richTextBox1.Text + ";";
SqlCommand comCheck1 = new SqlCommand(Check, Important.conex);
if (this.textBox1.Text == "")
{
this.textBox1.Text = comCheck1.ExecuteScalar().ToString();
}
Check = "select Username from _User where " + this.richTextBox1.Text + ";";
SqlCommand comCheck2 = new SqlCommand(Check, Important.conex);
if (this.textBox2.Text == "")
{
this.textBox2.Text = comCheck2.ExecuteScalar().ToString();
}
Check = "select Email from _User where " + this.richTextBox1.Text + ";";
SqlCommand comCheck3 = new SqlCommand(Check, Important.conex);
if (this.textBox3.Text == "")
{
this.textBox3.Text = comCheck3.ExecuteScalar().ToString();
}
Check = "select Password from _User where " + this.richTextBox1.Text + ";";
SqlCommand comCheck4 = new SqlCommand(Check, Important.conex);
if (this.textBox4.Text == "")
{
this.textBox4.Text = comCheck4.ExecuteScalar().ToString();
}
Check = "select UserType from _User where " + this.richTextBox1.Text + ";";
SqlCommand comCheck5 = new SqlCommand(Check, Important.conex);
if (this.textBox5.Text == "")
{
this.textBox5.Text = comCheck5.ExecuteScalar().ToString();
}
Check = "select Charge from _User where " + this.richTextBox1.Text + ";";
SqlCommand comCheck6 = new SqlCommand(Check, Important.conex);
if (this.textBox6.Text == "")
{
this.textBox6.Text = comCheck6.ExecuteScalar().ToString();
}
Update = "update _User set FullName = '" + this.textBox1.Text + "', Username = '******', Email = '" + this.textBox3.Text + "', Password = '******', UserType = '" + this.textBox5.Text + "', Charge = " + Convert.ToDouble(this.textBox6.Text)
+ " where " + this.richTextBox1.Text + ";";
SqlCommand upd = new SqlCommand(Update, Important.conex);
upd.ExecuteNonQuery();
}
catch (Exception ex)
{
Important.message = ex.GetType().ToString();
DialogMessageP dial = new DialogMessageP();
dial.ShowDialog();
}
Select = "select * from _User";
SqlCommand sel = new SqlCommand(Select, Important.conex);
SqlDataAdapter da = new SqlDataAdapter(sel);
DataSet ds = new DataSet();
da.Fill(ds);
dataGridView1.DataSource = ds.Tables[0];
this.textBox1.Text = "";
this.textBox6.Text = "";
this.textBox2.Text = "";
this.textBox3.Text = "";
this.textBox4.Text = "";
this.textBox5.Text = "";
this.richTextBox1.Text = "";
}
