/// <summary>
/// Returns a JSON string based on the contents of the database and user input
/// </summary>
/// <param name="sProcName">the name of the stored proc to invoke.</param>
/// <param name="filterValue">The value to feed to the stored proc</param>
/// <param name="fieldNameForDisplayedValue">The name of the field to read from the datareader;
/// This value is what will be displayed in the auto-complete box. This is the column whose matches
/// you are expecting from the datatable.
/// If you only provide this value, you get:
/// ["value_0",...,"value_N-1"]</param>
/// <param name="fieldNameForInsertedValue">Optional. The name of the field to read from the datareader;
/// This value is what will be inserted into the auto-complete box when you select fieldNameForDisplayedValue.
/// When your provide this value, you get:
/// [
/// {label:"dR[fieldNameForDisplayedValue]_0", value:"dR[fieldNameForInsertedValue]_0"},
/// {label:"dR[fieldNameForDisplayedValue]_N-1", value:"dR[fieldNameForInsertedValue]_N-1"},
/// ]</param>
/// <param name="ps">List of sql parameters required by the stored procedure.
/// If you use this parameter, you are responsible for providing all parameters required by the stored procedure,
/// including @filter. In this scenario, parameters filterValue and pName are ignored.</param>
/// <param name="pName">Parameter name; default is @filter</param>
/// <returns></returns>
public string[] GetJSONFromDB(string sProcName, string filterValue,
string fieldNameForDisplayedValue,
string fieldNameForInsertedValue = "",
string pName = "@filter",
List <SqlParameter> ps = null)
{
try
{
clsDB xdB = new clsDB();
SqlCommand cmd = new SqlCommand();
if (ps == null)
{
ps = new List <SqlParameter>();
ps.Add(new SqlParameter("@filter", filterValue));
}
List <string> lstResult = new List <string>();
using (xdB.OpenConnection())
{
using (SqlDataReader dR = (SqlDataReader)xdB.ExecuteSP(sProcName, ps, clsDB.SPExMode.READER, ref cmd))
{
if (dR != null && dR.HasRows)
{
while (dR.Read())
{
if (fieldNameForInsertedValue == "")
{
lstResult.Add(xdB.Fld2Str(dR[fieldNameForDisplayedValue]));
}
else
{
lstResult.Add("{label:" +
xdB.Fld2Str(dR[fieldNameForDisplayedValue]) +
", value:" +
xdB.Fld2Str(dR[fieldNameForInsertedValue]) +
"}");
}
}
}
}
}
if (lstResult.Count > 0)
{
return(lstResult.ToArray());
}
else
{
return(new string[0]);
}
} catch (Exception ex)
{
string strErr = ex.Message + ex.StackTrace;
return(new string[0]);
}
}
protected void Page_Load(object sender, EventArgs e)
{
clsDB conn = new clsDB();
DataTable dt;
String sql;
sql = "Select * from Account";
dt = conn.ExecuteDataTable(sql);
GridView1.DataSource = dt;
GridView1.DataBind();
}
public void Populate_EXCEL_DesignTables(clsFiles Files_In, clsProject Project_In,
clsOpCond OpCond_In, clsDB DB_In, string FilePath_In)
//============================================================================================
{
CloseExcelFiles();
// RADIAL:
// -------
Populate_tblMapping_Radial(Project_In, OpCond_In, DB_In); //....Software variable value field.
Populate_EXCEL_Radial(Files_In, Project_In, DB_In, FilePath_In); //....Design Table.
// BLANK Assy:
// -----------
Populate_tblMapping_Blank(Project_In, DB_In);
Populate_EXCEL_BlankAssy(Files_In, Project_In, DB_In, FilePath_In);
// SEAL:
// -----
if (Project_In.Product.EndConfig[0].Type == clsEndConfig.eType.Seal ||
Project_In.Product.EndConfig[1].Type == clsEndConfig.eType.Seal)
{
Populate_tblMapping_Seal(Project_In, DB_In);
Populate_EXCEL_Seal(Files_In, Project_In, DB_In, FilePath_In);
}
// TL THRUST BEARING:
// ------------------
if (Project_In.Product.EndConfig[0].Type == clsEndConfig.eType.Thrust_Bearing_TL ||
Project_In.Product.EndConfig[1].Type == clsEndConfig.eType.Thrust_Bearing_TL)
{
Populate_tblMapping_TL_TB(Project_In, DB_In);
Populate_EXCEL_TL_TB(Files_In, Project_In, DB_In, FilePath_In);
}
// THRUST BEARING ASSY:
// --------------------
if (Project_In.Product.EndConfig[0].Type == clsEndConfig.eType.Thrust_Bearing_TL ||
Project_In.Product.EndConfig[1].Type == clsEndConfig.eType.Thrust_Bearing_TL)
{
Populate_tblMapping_TB_Assy(Project_In, DB_In);
Populate_EXCEL_TB_Assy(Files_In, Project_In, DB_In, FilePath_In);
}
// COMPLETE ASSY:
// --------------
Populate_tblMapping_Complete(Project_In, OpCond_In, DB_In);
Populate_EXCEL_CompleteAssy(Files_In, Project_In, DB_In, FilePath_In);
// Solid Works Files.
// ------------------
CreateSWFiles(Project_In, Files_In, FilePath_In);
}
public static string[] InitializeLists(string FormName)
{
clsDB DB = new clsDB();
DataSet ds = DB.getDS("[dbo].[" + FormName + "_initialize]");
string[] arReturn = new string[ds.Tables.Count];
for (int t = 0; t < ds.Tables.Count; t++)
{
arReturn[t] = makeList(ds.Tables[t], "");
}
return(arReturn);
}
public static string CommonInitializeLists()
{
clsDB DB = new clsDB();
StringBuilder sb = new StringBuilder();
DataTable dt = DB.getDS("[dbo].[common_initialize]").Tables[0];
for (int i = 0; i < dt.Rows.Count; i++)
{
sb.Append("<label class='check-label'><input type=radio name=ContactNote value='" + lib.cStr(dt.Rows[i]["ContactNote_PK"]) + "'> " + lib.cStr(dt.Rows[i]["ContactNote"]) + "<span class='radio'></span></label>");
}
return(sb.ToString());
}
public static string checkUser(string usr)
{
clsDB DB = new clsDB();
if (lib.cInt(DB.getDS("um_checkUsername '" + usr + "'", true).Tables[0].Rows[0][0]) == 0)
{
return("");
}
else
{
return("Username already exists");
}
}
protected void btnTestDB_Click(object sender, EventArgs e)
{
clsDB db = new clsDB("");
if (db.TestConnection())
{
lblTestResult.Text = "Test OK";
}
else
{
lblTestResult.Text = "Test Failed.";
}
}
private string[] mDwg_Title_Suffix = new string[8]; //....1-based
#endregion
#region "CLASS METHODS:"
//----------------------
////public void PopulateAndMove_SWFiles_ProjectFolder(clsFiles Files_In, clsProject Project_In,
//// clsOpCond OpCond_In, clsDB DB_In, string FilePath_In)
//////====================================================================================================
////{
//// //....SWFiles - Design tables, Model & Drawing files.
//// //
//// PopulateAndMove_DesignTables(Files_In, Project_In, OpCond_In, DB_In, FilePath_In);
//// CopyAndMove_SW_Model_Dwg_Files(Project_In, Files_In, FilePath_In);
////}
#region "DESIGN TABLES POPULATION:"
private void PopulateAndMove_DesignTables(clsFiles Files_In, clsProject Project_In,
clsOpCond OpCond_In, clsDB DB_In, string FilePath_In)
//===============================================================================================
{
//....This routine copies the design table templates to the project design tables, populates them
//........and places them in the given project folder.
Close_DesignTables();
// RADIAL:
// -------
Populate_tblMapping_Radial(Project_In, OpCond_In, DB_In); //....Software variable value field.
PopulateAndMove_DT_Radial(Files_In, Project_In, DB_In, FilePath_In); //....Design Table.
// BLANK Assy:
// -----------
Populate_tblMapping_Blank(Project_In, DB_In);
PopulateAndMove_DT_BlankAssy(Files_In, Project_In, DB_In, FilePath_In);
// SEAL:
// -----
if (Project_In.Product.EndConfig[0].Type == clsEndConfig.eType.Seal ||
Project_In.Product.EndConfig[1].Type == clsEndConfig.eType.Seal)
{
Populate_tblMapping_Seal(Project_In, DB_In);
PopulateAndMove_DT_Seal(Files_In, Project_In, DB_In, FilePath_In);
}
// TL THRUST BEARING:
// ------------------
if (Project_In.Product.EndConfig[0].Type == clsEndConfig.eType.Thrust_Bearing_TL ||
Project_In.Product.EndConfig[1].Type == clsEndConfig.eType.Thrust_Bearing_TL)
{
Populate_tblMapping_TL_TB(Project_In, DB_In);
PopulateAndMove_DT_TL_TB(Files_In, Project_In, DB_In, FilePath_In);
}
// THRUST BEARING ASSY:
// --------------------
if (Project_In.Product.EndConfig[0].Type == clsEndConfig.eType.Thrust_Bearing_TL ||
Project_In.Product.EndConfig[1].Type == clsEndConfig.eType.Thrust_Bearing_TL)
{
Populate_tblMapping_TB_Assy(Project_In, DB_In);
PopulateAndMove_DT_TB_Assy(Files_In, Project_In, DB_In, FilePath_In);
}
// COMPLETE ASSY:
// --------------
Populate_tblMapping_Complete(Project_In, OpCond_In, DB_In);
PopulateAndMove_DT_CompleteAssy(Files_In, Project_In, DB_In, FilePath_In);
}
/// <summary>
/// Looks for searchVal in d; if not found, looks in database. If found, records value in d; if not, returns default value
/// </summary>
/// <param name="d">Dictionary that we search for first. </param>
/// <param name="searchVal">The value (key) in the dictionary we are searching for; if found, we get d(key)</param>
/// <param name="spName">The stored proc to use if searchVal is not in the dictionary</param>
/// <param name="fldName_key">The database field name to use as the dictionary key</param>
/// <param name="fldName_value">The database field name to use as the dictionary value</param>
/// <param name="defaultValue">The default value to return if not searchVal not found in either the dictioanry
/// or the database</param>
/// <param name="spParamName">Name of the parameter in the stored procedure. Leave this blank, and the function will use
/// @[fldName_key]</param>
/// <param name="putDefaultInDictionary">If not found in the dictionary or in the database, then if this set TRUE, searchVal
/// is put in the database with the default value, so that you don't query the database for the same searchVal next time this
/// function is called</param>
/// <returns></returns>
public string GetDBValueFromDictionary(ref Dictionary <string, string> d,
string searchVal,
string spName,
string fldName_key,
string fldName_value,
string defaultValue,
string spParamName = "",
Boolean putDefaultInDictionary = true)
{
try
{
if (d.ContainsKey(searchVal))
{
return(d[searchVal]);
}
else
{
clsDB xDB = new clsDB();
List <SqlParameter> ps = new List <SqlParameter>();
SqlCommand cmd = new SqlCommand();
if (spParamName == "")
{
spParamName = "@" + fldName_key;
}
ps.Add(new SqlParameter(spParamName, searchVal));
using (xDB.OpenConnection())
{
using (SqlDataReader dR = (SqlDataReader)xDB.ExecuteSP(spName, ps, clsDB.SPExMode.READER, ref cmd))
{
if (dR != null && dR.HasRows)
{
dR.Read();
string foundVal = xDB.Fld2Str(dR[fldName_value]);
d.Add(searchVal, foundVal);
return(foundVal);
}
}
}
}
//if we made it this far, we should return the default value. BUT:
if (putDefaultInDictionary)
{
d.Add(searchVal, defaultValue);
}
return(defaultValue);
} catch (Exception ex)
{
return(defaultValue);
}
}
public static string getReport(string formType, int formStatus, int dateRangeType, string dateRange)
{
try
{
clsDB DB = new clsDB();
string dateFrom = "null";
string dateTo = "null";
if (dateRangeType == 1)
{
string[] arDate = dateRange.Split('-');
dateFrom = "'" + (new DateTime(Convert.ToInt16(arDate[0]), Convert.ToInt16(arDate[1]), 1)).ToString("yyyy-MM-dd") + "'";
dateTo = "'" + (new DateTime(Convert.ToInt16(arDate[0]), Convert.ToInt16(arDate[1]), 1).AddMonths(1).AddDays(-1)).ToString("yyyy-MM-dd") + "'";
}
else
{
string[] arDate = dateRange.Split('~');
dateFrom = toDate(arDate[0]);
dateTo = toDate(arDate[1]);
}
DataSet ds = DB.getDS("EXEC report_Patient @formType='" + formType + "', @formStatus=" + formStatus + ", @dateFrom=" + dateFrom + ", @dateTo =" + dateTo + "", true);
DataTable dt = ds.Tables[0];
if (dt.Rows.Count == 0)
{
return("<div class='no-result'>No data available for selected filters</div>");
}
StringBuilder sbOutput = new StringBuilder();
sbOutput.Append("<table class='rpt_tbl tp_rht'>");
sbOutput.Append("<tr>");
sbOutput.Append("<th>Patient ID</th>");
sbOutput.Append("<th>Patient Name</th>");
sbOutput.Append("<th>DOB</th>");
sbOutput.Append("<th>Discharge Date</th>");
sbOutput.Append("<th>Form</th>");
sbOutput.Append("<th>Last Record Follow-up</th>");
sbOutput.Append("<th>Agent Name</th>");
sbOutput.Append("</tr>");
for (int i = 0; i < dt.Rows.Count; i++)
{
sbOutput.Append("<tr><td>" + lib.cStr(dt.Rows[i]["Patient ID"]) + "</td><td>" + lib.cStr(dt.Rows[i]["Patient Name"]) + "</td><td>" + lib.cDate(dt.Rows[i]["DOB"]) + "</td><td>" + lib.cDate(dt.Rows[i]["Discharge Date"]) + "</td><td>" + lib.cStr(dt.Rows[i]["Form"]) + "</td><td>" + lib.cStr(dt.Rows[i]["Last Recorded Follow-up"]) + "</td><td>" + lib.cStr(dt.Rows[i]["Agent Name"]) + "</td></tr>");
}
sbOutput.Append("</table>");
return(sbOutput.ToString());
}
catch (Exception ex) {
return(ex.Message.ToString());
}
}
/*****
* Purpose: Save textbox info as a record.
*
* Parameter list:
* object sender control that caused the event
* EventArgs e details about the sender
*
* Return value:
* void
******/
private void btnSave_Click(object sender, EventArgs e)
{
int status;
string sqlCommand;
OleDbConnection myDB = new OleDbConnection();
OleDbCommand myCommand;
clsDB myData = new clsDB(dbName);
try
{
myDB.ConnectionString = myData.getConnectString + dbName;
myDB.Open();
if (chkStatus.Checked == true)
{
status = 1;
}
else
{
status = 0;
}
// Build UPDATE command for this form
sqlCommand = "UPDATE " + dbTableName + " SET ";
sqlCommand += "FirstName = '" + txtFirstName.Text + "'," +
"MI = '" + txtMI.Text + "'," +
"LastName = '" + txtLastName.Text + "'," +
"Addr1 = '" + txtAddr1.Text + "'," +
"Addr2 = '" + txtAddr2.Text + "'," +
"City = '" + txtCity.Text + "'," +
"State = '" + txtState.Text + "'," +
"Zip = '" + txtZip.Text + "'," +
"HomePhone = '" + txtHome.Text + "'," +
"CellPhone = '" + txtCell.Text + "'," +
"WorkPhone = '" + txtWork.Text + "'," +
"Email = '" + txtEmail.Text + "'," +
"Birthday = '" + txtBirthday.Text + "'," +
"Anniversary = '" + txtAnniversary.Text + "'," +
"Status = " + status.ToString() +
" WHERE ID = " + txtRecordID.Text;
myCommand = new OleDbCommand(sqlCommand, myDB);
myCommand.ExecuteNonQuery();
MessageBox.Show("Record added successfully.");
myDB.Close();
} catch (Exception ex)
{
MessageBox.Show("Failed to save edit data. Error: " + ex.Message, "Process Error");
}
}
private void mnuOpen_Click(object sender, EventArgs e)
{
OpenFileDialog fileOpen = new OpenFileDialog();
fileOpen.InitialDirectory = Application.StartupPath;
fileOpen.Title = "Select file to open:";
fileOpen.Filter = "(*.mdb)|*.mdb|All files (*.*)|*.*";
if (fileOpen.ShowDialog() == DialogResult.OK)
{
dbName = fileOpen.FileName;
this.Text += ": Open database = " + dbName;
myDB = new clsDB(dbName);
}
}
public static string saveHospital(string id, string HospitalName, string Address, string ZipCode, string ContactNumber)
{
clsDB DB = new clsDB();
try
{
ZipCode = Int32.Parse(ZipCode).ToString();
}
catch {
ZipCode = "null";
}
DB.executeSQL("EXEC hm_saveHospital @Id='" + id + "', @HospitalName='" + HospitalName + "', @Address='" + Address + "', @ZipCode=" + ZipCode + ", @ContactNumber='" + @ContactNumber + "'");
DB.closeConnection();
return("");
}
public static string[] doLogin(string usr, string pwd)
{
string[] arRetrun = new string[2];
arRetrun[1] = "";
clsDB DB = new clsDB();
DataTable dtUser = DB.getDS("SELECT * FROM tblUser WHERE Username = '******'", "") + "'", true).Tables[0];
if (dtUser.Rows.Count == 0)
{
arRetrun[0] = "Invalid username or password";
}
else if (dtUser.Rows[0]["Password"].ToString() == pwd)
{
if (Convert.ToBoolean(dtUser.Rows[0]["IsActive"]))
{
clsUser Usr = new clsUser(dtUser.Rows[0]);
DataSet dsPermissions = DB.getDS("um_getPages " + Usr.User_PK + "," + (Usr.IsAdmin ? 1 : 0), true);
Usr.setPermissions(dsPermissions, portalVersion.info());
if (config.SetConfigSession(dsPermissions.Tables[1], dsPermissions.Tables[2], DB))
{
if (lib.cBool(dtUser.Rows[0]["IsChangePasswordOnFirstLogin"]))
{
arRetrun[0] = "expired";
}
if (lib.cInt(DB.getDS("SELECT DATEDIFF(day,MAX(dtPassword),GetDate()) FROM tblUserPasswordLog WHERE user_pk=" + Usr.User_PK + "").Tables[0].Rows[0][0]) > config.PasswordLifeInDays())
{
arRetrun[0] = "expired";
}
arRetrun[0] = "success";
}
else
{
arRetrun[0] = "System not configured, please contact system administrator or support staff";
}
}
else
{
arRetrun[0] = "User locked, please contact system administrator or support staff";
}
}
else
{
arRetrun[0] = "Invalid username or password";
}
return(arRetrun);
}
public static string saveForm(int PK, int Patient_PK, int PCP_PK, string DischargeDate, string Insurance, string FU_Data, string FormName)
{
clsUser Usr = new clsUser();
clsDB DB = new clsDB();
string sql = "EXEC common_saveForm @PK=" + PK + ", @Patient_PK=" + Patient_PK + ", @PCP_PK=" + PCP_PK + ", @DischargeDate=" + (DischargeDate == "" ? "null" : "'" + DischargeDate + "'") + ", @Insurance=" + (Insurance == "null" ? "null" : "'" + Insurance + "'") + ", @FormName='" + FormName + "',@User="******"0")
{
sb_sql.Append("IF NOT EXISTS(SELECT 1 FROM tblForm" + FormName + " WHERE Form_PK=" + PK + " AND Followup_PK=" + arData[0] + ") INSERT INTO tblForm" + FormName + "(Form_PK,Followup_PK,dtCreated,Created_User_PK) VALUES(" + PK + "," + arData[0] + ",GetDate()," + Usr.User_PK + "); ");
sb_sql.Append("UPDATE tblForm" + FormName + " SET dtLastUpdated=GetDate(),LastUpdated_User_PK=" + Usr.User_PK + "");
for (int d = 2; d < arData.Length; d++)
{
string[] arCol = arData[d].Split('|');
if (arCol[0].Substring(0, 2) != "Is" && arCol[0].Substring(arCol[0].Length - 2, 2) != "PK")
{
if (arCol[1] == "null")
{
sb_sql.Append(", " + arCol[0] + "=" + arCol[1]);
}
else
{
sb_sql.Append(", " + arCol[0] + "='" + arCol[1].Replace("'", "") + "'");
}
}
else
{
sb_sql.Append(", " + arCol[0] + "=" + arCol[1]);
}
}
sb_sql.Append(" WHERE Form_PK=" + PK + " AND Followup_PK=" + arData[0] + ";");
}
else
{
sb_sql.Append("DELETE FROM tblForm" + FormName + " WHERE Form_PK=" + PK + " AND Followup_PK=" + arData[0] + "; ");
}
}
sb_sql.Append("EXEC saveContactLog @FormPK=" + PK + ", @NotePK=1, @AdditionDetail='', @User="******";");
DB.executeSQL(sb_sql.ToString());
return(sb_sql.ToString());
}
public static string verify_session()
{
string username = "";
try { username = HttpContext.Current.Session["Username"].ToString(); }
catch { username = ""; }
if (username == "")
{
clsDB DB = new clsDB();
if (DB.getDS("SELECT 1 FROM config.appSettings WHERE KeyName='UseSSOTokenForAuthentication' and KeyValue='true'", true).Tables[0].Rows.Count == 1)
{
return("SSO");
}
}
return(username);
}
public static string savePatient(string PK, string Patient_ID, string Lastname, string Firstname, string DOB, string PhoneNumber, string AlternatePhoneNumber, string Email)
{
clsDB DB = new clsDB();
DataSet ds = DB.getDS("EXEC common_savePatient @PK=" + PK + ", @Patient_ID='" + Patient_ID + "', @Lastname='" + Lastname + "', @Firstname='" + Firstname + "', @DOB=" + (DOB == ""?"null":"'" + DOB + "'") + ", @PhoneNumber='" + PhoneNumber + "', @AlternatePhoneNumber='" + AlternatePhoneNumber + "', @Email='" + Email + "'", true);
DB.closeConnection();
if (ds.Tables.Count == 0)
{
return("");
}
else
{
DataRow dr = ds.Tables[0].Rows[0];
return(lib.cStr(dr["Patient_PK"]) + "~" + lib.cStr(dr["Patient_ID"]) + "~" + lib.cStr(dr["Firstname"]) + "~" + lib.cStr(dr["Lastname"]) + "~" + lib.cDate(dr["DOB"]) + "~" + lib.cStr(dr["PhoneNumber"]) + "~" + lib.cStr(dr["AlternatePhoneNumber"]) + "~" + lib.cStr(dr["Email"]));
}
}
public static string savePCP(string PK, string PCP_ID, string Lastname, string Firstname)
{
clsDB DB = new clsDB();
DataSet ds = DB.getDS("EXEC common_savePCP @PK=" + PK + ", @PCP_ID='" + PCP_ID + "', @Lastname='" + Lastname + "', @Firstname='" + Firstname + "'", true);
DB.closeConnection();
if (ds.Tables.Count == 0)
{
return("");
}
else
{
DataRow dr = ds.Tables[0].Rows[0];
return(lib.cStr(dr["Provider_PK"]) + "~" + lib.cStr(dr["Provider_ID"]) + "~" + lib.cStr(dr["Firstname"]) + "~" + lib.cStr(dr["Lastname"]));
}
}
public static string getContactLog(int PK)
{
clsDB DB = new clsDB();
StringBuilder sb = new StringBuilder();
DataTable dt = DB.getDS("EXEC getContactLog @FormPK=" + PK).Tables[0];
if (dt.Rows.Count == 0)
{
return("No log available");
}
for (int i = 0; i < dt.Rows.Count; i++)
{
sb.Append("<h6>" + lib.cStr(dt.Rows[i]["Coder"]) + " on " + lib.cDateTime(dt.Rows[i]["dtInsert"]) + "</h6><div>" + lib.cStr(dt.Rows[i]["ContactNote"]) + "</div>");
}
return(sb.ToString());
}
public static string searchAddZip(string zip)
{
string sSQL = "select distinct ZC.ZipCode from tblZipCode ZC";
zip = zip.ToUpper();
if (zip.IndexOf(" TO ") > 0)
{
sSQL += " WHERE zipcode >= '" + zip.Split(new string[] { " TO " }, StringSplitOptions.None)[0] + "' AND zipcode <= '" + zip.Split(new string[] { " TO " }, StringSplitOptions.None)[1] + "'";
}
else if (zip.IndexOf("C") > 0)
{
sSQL += " INNER JOIN tblZipcode T ON T.zipcode='" + zip.Replace("C", "") + "' AND T.State=ZC.State AND T.County=ZC.County AND T.City=ZC.City";
}
else if (zip.IndexOf("K") > 0)
{
sSQL += " INNER JOIN tblZipcode T ON T.zipcode='" + zip.Replace("K", "") + "' AND T.State=ZC.State AND T.County=ZC.County";
}
else if (zip.IndexOf("S") > 0)
{
sSQL += " INNER JOIN tblZipcode T ON T.zipcode='" + zip.Replace("S", "") + "' AND T.State=ZC.State";
}
else if (zip.IndexOf("Z") > 0)
{
sSQL += " INNER JOIN tblZoneZipcode T ON T.ZipCode_PK=ZC.ZipCode_PK";
sSQL += " INNER JOIN tblZoneZipcode ZZC ON T.Zone_PK = ZZC.Zone_PK AND ZZC.ZipCode_PK=" + zip.Replace("Z", "");
}
else
{
sSQL += " WHERE zipcode like '" + zip.ToLower().Split(' ')[0] + "%'";
}
string output = "";
clsDB DB = new clsDB();
DataTable dt = DB.getDS(sSQL, true).Tables[0];
for (int i = 0; i < dt.Rows.Count; i++)
{
if (i > 0)
{
output += ",";
}
output += lib.cStr(dt.Rows[i]["ZipCode"]);
}
return(output);
}
private void PopulateData()
{
clsDB DB = new clsDB();
DataTable dtData;
int iTalepKodu = 0;
iTalepKodu = int.Parse(Request["TalepKodu"].ToString());
dtData = DB.GetTalepTeshisleri(iTalepKodu);
rptrTeshisler.DataSource = dtData;
rptrTeshisler.DataBind();
if (dtData.Rows.Count == 0)
{
Response.Redirect("wfDiagnosis.aspx?NoResults=1");
}
}
public static Double DVal(String D_Desig_In)
//==========================================
{
//....Returns Diameter in inch.
clsDB pDB = new clsDB();
//BearingDBEntities pBearingDBEntities = new BearingDBEntities();
Double pD = 0.0F;
if (D_Desig_In != null)
{
string pstrFIELDS, pstrFROM, pstrSQL, pstrWHERE, pstrORDERBY;
OleDbConnection pConnection = null;
pstrFIELDS = "Select D";
pstrFROM = " FROM [Drill$]";
pstrWHERE = " WHERE D_Desig = '" + D_Desig_In + "'";
pstrORDERBY = " Order by D ASC";
pstrSQL = pstrFIELDS + pstrFROM + pstrWHERE + pstrORDERBY;
OleDbDataReader pobjDR = null;
pobjDR = modMain.gDB.GetDataReader(pstrSQL, modMain.gFiles.FileTitle_EXCEL_StdToolData, ref pConnection);
if (pobjDR.Read())
{
pD = pDB.CheckDBDouble(pobjDR["D"]);
}
else if (!D_Desig_In.Contains("/"))
{
pD = modMain.ConvTextToDouble(D_Desig_In);
}
pobjDR.Dispose();
pConnection.Close();
//var pProject = (from pRec in pBearingDBEntities.tblManf_Drill where pRec.fldD_Desig == D_Desig_In select pRec.fldD).ToList();
//if (pProject.Count > 0)
// pD = pDB.CheckDBDouble(pProject[0]);
//else if (!D_Desig_In.Contains("/"))
// pD = modMain.ConvTextToDouble(D_Desig_In);
}
return(pD);
}
private string SetEmailBody(string strBody, int intOrderID, int intCustomerID, ref string strSubject,
ref string strErr, ref string strOrderID, ref string strCustEmail, ref string strSenderEmailID, ref string strSenderEmailPwd)
{
clsDB objDB = null;
string strResult;
DataSet dstOutPut;
StringBuilder str = new StringBuilder(strBody);
StringBuilder strText = new StringBuilder("");
try
{
objDB = new clsDB();
objDB.AddParameter("p_intOrderID", intOrderID);
objDB.AddParameter("p_intCustomerID", intCustomerID);
dstOutPut = objDB.ExecuteSelect("spOrderDetailsEmail", CommandType.StoredProcedure, 0, ref strErr, "p_ErrMsg");
if (strErr != "")
{
strResult = "Error:" + strErr;
}
if (dstOutPut != null && dstOutPut.Tables.Count > 0)
{
strOrderID = Convert.ToString(dstOutPut.Tables[0].Rows[0]["OrderID"]);
strCustEmail = Convert.ToString(dstOutPut.Tables[0].Rows[0]["CustomerEmailID"]);
strSenderEmailID = Convert.ToString(dstOutPut.Tables[0].Rows[0]["SenderEmailID"]);
strSenderEmailPwd = System.Web.Configuration.WebConfigurationManager.AppSettings["SenderPwd"];
strSubject = Convert.ToString(dstOutPut.Tables[0].Rows[0]["CustomerName"]) + " Oredr Itinerary :: Order ID " + Convert.ToString(dstOutPut.Tables[0].Rows[0]["OrderID"]);
if (dstOutPut.Tables.Count > 0 && dstOutPut.Tables[0].Rows.Count > 0)
{
strText.Append("<tr><td >Your Order is Booked Successfully</td></tr>");
}
//str= str.Append("<table width='600px' border='0'>" + strText.ToString() + "</table>").ToString();
strBody = strText.ToString();
}
}
catch (Exception ex)
{
}
return(strBody);
}
protected void btnTespit_Click(object sender, EventArgs e)
{
clsDB DB = new clsDB();
int iTalepID = 0;
string sURL = "";
string sAdi, sSoyadi, sEmail, sAciklama;
sAdi = txtAdi.Text;
sSoyadi = txtSoyadi.Text;
sEmail = txtEmail.Text;
sAciklama = txtAciklama.Text;
iTalepID = DB.SaveTalep(sAdi, sSoyadi, sEmail, sAciklama);
clsKernel.Process(iTalepID);
sURL = "~/wfDiagnosisResult.aspx?TalepKodu=" + iTalepID.ToString();
Response.Redirect(sURL);
}
public static void Process(int iTalepID)
{
clsDB DB = new clsDB();
clsTeshis Teshis;
string sTeshisKosul = "", sTeshisAciklama = "";
int i = 0, iToplamSayi = 0;
DataTable dtTeshisler, dtTalepler;
DataRow drTeshis, drTalep;
dtTalepler = DB.GetTalepler(iTalepID);
drTalep = dtTalepler.Rows[0];
sHastaAciklama = drTalep["Aciklama"].ToString();
_HastaAciklamaCumleleri = clsUtilities.CumlelereBol(sHastaAciklama, _CumleAyraclari);
sTeshisKosul = clsUtilities.sTeshisKosulOlustur(_HastaAciklamaCumleleri);
dtTeshisler = DB.GetTeshis(sTeshisKosul);
_Teshisler.Clear();
for (i = 0; i < dtTeshisler.Rows.Count; i++)
{
Teshis = new clsTeshis();
drTeshis = dtTeshisler.Rows[i];
Teshis.iKodu = (int)drTeshis["Kodu"];
Teshis.sAdi = drTeshis["Adi"].ToString();
Teshis.iYuzde = int.Parse(Math.Round(double.Parse(drTeshis["Sayi"].ToString()) * 100, 0).ToString());
iToplamSayi += Teshis.iSayi;
sTeshisAciklama = "Yaptığınız açıklamaya göre <strong>%" + Teshis.iYuzde.ToString() + "</strong> olasılıkla <strong>" + Teshis.sAdi + "</strong> hastalığına sahipsiniz. ";
sTeshisAciklama = sTeshisAciklama + " En kısa zamanda gerekli tahlil ve kontrollerin yapılabilmesi için Hekiminize danışmanız tavsiye edilir. ";
sTeshisAciklama = sTeshisAciklama + " Burada verilen sonuçlar bilgilendirme amaçlı olup, bu uygulamada yer alan hiçbir ifade hekimlik hizmeti sunmayı veya ";
sTeshisAciklama = sTeshisAciklama + " hekim-hasta ilişkisi tesis etmeyi amaçlamamaktadır.";
DB.SaveTeshis(iTalepID, Teshis.iKodu, Teshis.iYuzde, sTeshisAciklama);
_Teshisler.Add(Teshis);
}
//for (i = 0; i < dtTeshisler.Rows.Count; i++)
//{
// _Teshisler[i].iYuzde = int.Parse(Math.Round(_Teshisler[i].iSayi * 100.00 / iToplamSayi,0).ToString());
// DB.SaveTeshis(iTalepID, _Teshisler[i].iKodu, _Teshisler[i].iYuzde, sTeshisAciklama);
//}
}
public static string getProfileInfo(int id)
{
clsDB DB = new clsDB();
DataSet ds = DB.getDS("um_getProfileInfo " + id + "", true);
DataTable dt = ds.Tables[0];
StringBuilder sbOutput = new StringBuilder();
sbOutput.Append("<h1>Profile Role:</h1>");
for (int i = 0; i < dt.Columns.Count; i++)
{
if (lib.cBool(dt.Rows[0][i]))
{
sbOutput.Append("<div class='fleft s4'>" + dt.Columns[i].ColumnName + "</div>");
}
}
sbOutput.Append("<br class='clear'>");
return(sbOutput.ToString());
}
public void InsertLayCutHD()
{
string errMsg = "";
clsDB obj = new clsDB();
SortedList sl = new SortedList();
Int32 i = 0;
try
{
sl.Add("@LayerBatchNo", LayerBatchNo);
sl.Add("@compcode", compcode);
sl.Add("@loccode", loccode);
sl.Add("@BunNoMethod", BunNoMethod);
sl.Add("@CutDate", CutDate);
sl.Add("@JobOrderNo", JobOrderNo);
sl.Add("@StyleCode", StyleCode);
sl.Add("@AVGConsumption", AVGConsumption);
sl.Add("@PCSPerBundle", PCSPerBundle);
sl.Add("@MerchandiserCode", MerchandiserCode);
sl.Add("@CuttingMasterCode", CuttingMasterCode);
sl.Add("@QCHeadCode", QCHeadCode);
sl.Add("@BundleStartNo", BundleStartNo);
sl.Add("@BundleEndNo", BundleEndNo);
sl.Add("@createdby", createdby);
sl.Add("@modifiedby", modifiedby);
sl.Add("@createdDate", createdDate);
sl.Add("@modifiedDate", modifiedDate);
sl.Add("@TransferFlag", TransferFlag);
sl.Add("@DeletedSataus", DeletedSataus);
sl.Add("@CanGenerateBundle", CanGenerateBundle);
sl.Add("@BundleCreated", BundleCreated);
i = obj.ExecuteNonQuery("iLayerCutHD", sl);
}
catch (Exception ex)
{
errMsg = ex.Message;
}
finally
{
obj = null;
}
}
public DataSet sBundleNumberFC()
{
clsDB obj = new clsDB();
string errMsg = "";
DataSet ds = new DataSet();
try
{
ds = obj.GetData("sBundleNumberFC", null);
}
catch (Exception ex)
{
errMsg = ex.Message;
}
finally
{
obj = null;
}
return(ds);
}
public DataSet sCutting()
{
clsDB obj = new clsDB();
string errMsg = "";
DataSet ds = new DataSet();
try
{
ds = obj.GetData("sMerchandiser", null);
}
catch (Exception ex)
{
errMsg = ex.Message;
}
finally
{
obj = null;
}
return(ds);
}
public DataSet GetLayerInfo()
{
clsDB obj = new clsDB();
string errMsg = "";
DataSet ds = new DataSet();
try
{
ds = obj.GetData("sLayInfo", null);
}
catch (Exception ex)
{
errMsg = ex.Message;
}
finally
{
obj = null;
}
return(ds);
}
static void Main(string[] args)
{
try
{
clsDB dbObj = new clsDB();
DataSet ds = dbObj.ExecuteSelect("CMS_PULL_GET_NEW_TRIPS_FOR_CRS_INFO", CommandType.StoredProcedure, 160);
int cnt = 0;
string[] arrTrip_info = new string[ds.Tables[0].Rows.Count];
int[] arrTrip_ids = new int[ds.Tables[0].Rows.Count];
int[] arrCompany_ids = new int[ds.Tables[0].Rows.Count];
int intTrip_id;
int intCompanyId;
foreach (DataRow dataRow in ds.Tables[0].Rows)
{
try
{
using (WebClient client = new WebClient())
{
string webUrl = ConfigurationSettings.AppSettings["WebserviceURL"].ToString();
intTrip_id = Convert.ToInt32(dataRow["TRIP_ID"]);
intCompanyId=Convert.ToInt32(dataRow["COMPANY_ID"]);
webUrl += "&KeyCode=''&intTripID=" + intTrip_id.ToString();
webUrl += "&dtChartDate=" + ((DateTime)dataRow["CHART_DATE"]).ToString("yyyy-MMM-dd").ToString();
byte[] obj = client.DownloadData(webUrl);
string json = Encoding.ASCII.GetString(obj);
arrTrip_info[cnt] = json;
arrTrip_ids[cnt] = intTrip_id;
arrCompany_ids[cnt] = intCompanyId;
}
}
catch (System.Exception es) { }
cnt++;
}
int maxRow = 100;
DataTable dt = new DataTable();
//dt.TableName = "TRIP_INFO";
dt.Columns.Add("TRIP_ID", typeof(int));
dt.Columns.Add("COMPANY_ID", typeof(int));
dt.Columns.Add("ROUTE_INFO", typeof(string));
dt.Columns.Add("DEPARTURE_TIME", typeof(DateTime));
for (int i = 0; i < arrTrip_info.Length; i++)
{
if (arrTrip_info[i].Length > 0)
{
string ctName = "";
string deprTime = "";
try
{
JObject tripInfo = JObject.Parse(arrTrip_info[i]);
var cts = tripInfo["APITripsSummaryResult"].Children();
foreach (var ct in cts)
{
ctName += ct["CityName"].ToString().Trim('"') + "-";
if (ct["Position"].ToString().Trim('"') == "1")
deprTime = ct["DepartureTime"].ToString().Trim('"');
}
}
catch (System.Exception ex) { }
DataRow dr = dt.NewRow();
dr["TRIP_ID"] = arrTrip_ids[i];
dr["COMPANY_ID"] = arrCompany_ids[i];
dr["ROUTE_INFO"] = ctName.Remove(ctName.Length - 1);
dr["DEPARTURE_TIME"] = Convert.ToDateTime(deprTime);
dt.Rows.Add(dr);
if (dt.Rows.Count == maxRow)
{
try
{
dbObj = null;
dbObj = new clsDB();
dbObj.AddParameter("TRIP_INFO", dt);
dbObj.ExecuteDML("CMS_PULL_UPDATE_TRIP_INFO", CommandType.StoredProcedure, 180);
dt.Rows.Clear();
}
catch (System.Exception ex) { }
}
}
}
if (dt.Rows.Count > 0)
{
try
{
dbObj = null;
dbObj = new clsDB();
dbObj.AddParameter("TRIP_INFO", dt);
dbObj.ExecuteDML("CMS_PULL_UPDATE_TRIP_INFO", CommandType.StoredProcedure, 180);
dt.Rows.Clear();
}
catch (System.Exception ex) { }
}
}
catch (System.Exception ex)
{
}
}
