/// <summary>
///
/// select a set of rows from table t_RBSR_AUFW_u_BusRoleOwner.
/// </summary>
/// <param name="maxRowsToReturn">Max number of rows to return. If null or 0 all rows are returned.</param>
/// <param name="BusRoleID"></param>
/// <returns>returnListBusRoleOwnerByBusRole[]</returns>
public returnListBusRoleOwnerByBusRole[] ListBusRoleOwnerByBusRole(int?maxRowsToReturn, int BusRoleID)
{
returnListBusRoleOwnerByBusRole[] rv = null;
DBConnect();
OdbcCommand cmd = _dbConnection.CreateCommand();
if (maxRowsToReturn.HasValue && maxRowsToReturn.Value > 0)
{
if (_dbConnection.Driver.ToLower().StartsWith("sql"))
{
cmd.CommandText = "SELECT TOP " + maxRowsToReturn.Value + " \"c_id\", \"c_u_EID\", \"c_u_Geography\", \"c_u_Rank\", \"c_r_BusRole\", (SELECT ForDisplay FROM DICT_RoleOwnerType where Abbrev=c_u_Rank) as Friendly, (SELECT Sequence FROM DICT_RoleOwnerType where Abbrev=c_u_Rank) as Sequence FROM \"t_RBSR_AUFW_u_BusRoleOwner\" WHERE \"c_r_BusRole\"=? ORDER BY Sequence";
}
else
{
cmd.CommandText = "SELECT \"c_id\", \"c_u_EID\", \"c_u_Geography\", \"c_u_Rank\", \"c_r_BusRole\", (SELECT ForDisplay FROM DICT_RoleOwnerType where Abbrev=c_u_Rank) as Friendly, (SELECT Sequence FROM DICT_RoleOwnerType where Abbrev=c_u_Rank) as Sequence FROM \"t_RBSR_AUFW_u_BusRoleOwner\" WHERE \"c_r_BusRole\"=?" + " ORDER BY Sequence LIMIT " + maxRowsToReturn.Value;
}
}
else
{
cmd.CommandText = "SELECT \"c_id\", \"c_u_EID\", \"c_u_Geography\", \"c_u_Rank\", \"c_r_BusRole\", (SELECT ForDisplay FROM DICT_RoleOwnerType where Abbrev=c_u_Rank) as Friendly FROM \"t_RBSR_AUFW_u_BusRoleOwner\" WHERE \"c_r_BusRole\"=?";
}
cmd.Parameters.Add("1_BusRoleID", OdbcType.Int);
cmd.Parameters["1_BusRoleID"].Value = (object)BusRoleID;
OdbcDataReader dr = cmd.ExecuteReader();
List <returnListBusRoleOwnerByBusRole> rvl = new List <returnListBusRoleOwnerByBusRole>();
while (dr.Read())
{
returnListBusRoleOwnerByBusRole cr = new returnListBusRoleOwnerByBusRole();
if (dr.IsDBNull(0))
{
cmd.Dispose(); DBClose(); throw new Exception("Value 'null' is not allowed for 'ID'");
}
else
{
cr.ID = dr.GetInt32(0);
}
if (dr.IsDBNull(1))
{
cmd.Dispose(); DBClose(); throw new Exception("Value 'null' is not allowed for 'EID'");
}
else
{
cr.EID = dr.GetString(1);
}
if (dr.IsDBNull(2))
{
cmd.Dispose(); DBClose(); throw new Exception("Value 'null' is not allowed for 'Geography'");
}
else
{
cr.Geography = dr.GetString(2);
}
if (dr.IsDBNull(3))
{
cmd.Dispose(); DBClose(); throw new Exception("Value 'null' is not allowed for 'Rank'");
}
else
{
cr.Rank = dr.GetString(3);
}
if (dr.IsDBNull(4))
{
cmd.Dispose(); DBClose(); throw new Exception("Value 'null' is not allowed for 'BusRoleID'");
}
else
{
cr.BusRoleID = dr.GetInt32(4);
}
cr.RankFriendly = dr.GetString(5);
rvl.Add(cr);
}
dr.Close();
dr.Dispose();
rv = rvl.ToArray();
cmd.Dispose();
DBClose();
return(rv);
}
/// <summary>
/// Implementation of <see cref="IWorkflowScript.OnWorkflowScriptExecute" />.
/// <seealso cref="IWorkflowScript" />
/// </summary>
/// <param name="app"></param>
/// <param name="args"></param>
public void OnWorkflowScriptExecute(Hyland.Unity.Application app, Hyland.Unity.WorkflowEventArgs args)
{
try
{
// Initialize global settings
IntializeScript(ref app, ref args);
//get and clean LicenseType and Application # keywords for passing to LicEase database
KeywordType kwtAppNum = _currentDocument.DocumentType.KeywordRecordTypes.FindKeywordType(gParamAppNum);
string strAppNum = "";
if (kwtAppNum != null)
{
KeywordRecord keyRecFileNum = _currentDocument.KeywordRecords.Find(kwtAppNum);
if (keyRecFileNum != null)
{
Keyword kwdFileNum = keyRecFileNum.Keywords.Find(kwtAppNum);
if (kwdFileNum != null)
{
strAppNum = CleanSeedKW(kwdFileNum.ToString());
}
}
}
KeywordType kwtLicenseType = _currentDocument.DocumentType.KeywordRecordTypes.FindKeywordType(gParamLicType);
string strLicenseType = "";
if (kwtLicenseType != null)
{
KeywordRecord keyRecLicenseType = _currentDocument.KeywordRecords.Find(kwtLicenseType);
if (keyRecLicenseType != null)
{
Keyword kwdLicenseType = keyRecLicenseType.Keywords.Find(kwtLicenseType);
if (kwdLicenseType != null)
{
strLicenseType = CleanSeedKW(kwdLicenseType.ToString());
}
}
}
if ((strAppNum == "") || (strLicenseType == ""))
{
throw new Exception(string.Format("Either {0} or {1} is blank.", gParamAppNum, gParamLicType));
}
//access Config Item for LicEase User
string gUSER = "";
if (app.Configuration.TryGetValue("LicEaseUser", out gUSER))
{
}
//access Config Item for LicEase Password
string gPASS = "";
if (app.Configuration.TryGetValue("LicEasePassword", out gPASS))
{
}
/* COMMENT THIS SECTION OUT WHEN MOVING TO PROD */
//access Config Item for LicEase UAT ODBC
string gODBC = "";
if (app.Configuration.TryGetValue("LicEaseUAT", out gODBC))
{
}
/* UNCOMMENT THIS SECTION WHEN MOVING TO PROD
* //access Config Item for LicEase PROD ODBC
* string gODBC = "";
* if (app.Configuration.TryGetValue("LicEasePROD", out gODBC))
* {
* }
*/
string connectionString = string.Format("DSN={0};Uid={1};Pwd={2};", gODBC, gUSER, gPASS);
app.Diagnostics.WriteIf(Diagnostics.DiagnosticsLevel.Verbose, string.Format("Connection string: {0}", connectionString));
StringBuilder strSql = new StringBuilder();
strSql.Append(@"SELECT f.mod_desc AS PLANMOD FROM appl a, appl_mdf m, mdf f");
strSql.Append(@" WHERE a.applc_id = m.applc_id AND m.mod_id = f.mod_id AND");
strSql.Append(@" f.mod_typ = 'K' AND a.clnt_cde = '");
strSql.Append(strLicenseType);
strSql.Append(@"' AND a.applc_nbr = '");
strSql.Append(strAppNum);
strSql.Append(@"'");
app.Diagnostics.WriteIf(Diagnostics.DiagnosticsLevel.Verbose, string.Format("Sql Query: {0}", strSql.ToString()));
using (OdbcConnection con = new OdbcConnection(connectionString))
{
try
{
con.Open();
using (OdbcCommand command = new OdbcCommand(strSql.ToString(), con))
using (OdbcDataReader reader = command.ExecuteReader())
{
if (reader.HasRows)
{
string strMOD = "";
reader.Read();
strMOD = reader["PLANMOD"].ToString();
Keyword kwdMOD = null;
if (!String.IsNullOrEmpty(strMOD))
{
KeywordType kwtMOD = app.Core.KeywordTypes.Find(gSaveToPlanMod);
if (kwtMOD != null)
{
kwdMOD = CreateKeywordHelper(kwtMOD, strMOD);
}
}
using (DocumentLock documentLock = _currentDocument.LockDocument())
{
// Ensure lock was obtained
if (documentLock.Status != DocumentLockStatus.LockObtained)
{
throw new Exception("Document lock not obtained");
}
// Create keyword modifier object to hold keyword changes
KeywordModifier keyModifier = _currentDocument.CreateKeywordModifier();
// Add update keyword call to keyword modifier object
//Note Overloads available for use
//(I.E.): keyModifier.AddKeyword(keywordTypeName,keywordValue)
if (kwdMOD != null)
{
keyModifier.AddKeyword(kwdMOD);
}
// Apply keyword change to the document
keyModifier.ApplyChanges();
string output = String.Format("Keyword: '{0}' Value: '{1}', {3}added to Document {2}.",
gSaveToPlanMod, strMOD, _currentDocument.ID, Environment.NewLine);
//Output the results to the OnBase Diagnostics Console
app.Diagnostics.WriteIf(Hyland.Unity.Diagnostics.DiagnosticsLevel.Verbose, output);
}
}
else
{
throw new Exception(string.Format("No records found in database for {0}='{1}' and {4}{2}='{3}' ", gParamLicType, strLicenseType, gParamAppNum, strAppNum, Environment.NewLine));
}
}
}
catch (Exception ex)
{
throw new ApplicationException("Error during database operations!", ex);
}
finally
{
if (con.State == ConnectionState.Open)
{
con.Close();
}
}
}
}
catch (Exception ex)
{
// Handle exceptions and log to Diagnostics Console and document history
HandleException(ex, ref app, ref args);
}
finally
{
// Log script execution end
app.Diagnostics.WriteIf(Diagnostics.DiagnosticsLevel.Info,
string.Format("End Script - [{0}]", ScriptName));
}
}
public void Run(int plantID)
{
switch (plantID)
{
case 1:
plant = new Plant(plantID, "CA");
break;
case 3:
case 5:
plant = new Plant(plantID, "US");
break;
case 4:
plant = new Plant(plantID, "CP");
break;
default:
throw new Exception("Invalid plant ID: " + plantID.ToString());
}
ExcoCalendar calendar = new ExcoCalendar(DateTime.Today.Year - 2000, DateTime.Today.Month, false, plantID);
// get customer, currency, territory, plant and year/period
ExcoODBC database = ExcoODBC.Instance;
database.Open(Database.CMSDAT);
string query = "select dhinv#, dhbcs#, dhscs#, dhcurr, dhterr, dharyr, dharpr, dhord# from cmsdat.oih where dhpost='Y' and dharyr>=" + (calendar.GetFiscalYear() - 2).ToString() + " and dhplnt=" + plantID.ToString();
OdbcDataReader reader = database.RunQuery(query);
while (reader.Read())
{
int invNum = Convert.ToInt32(reader["dhinv#"]);
int soNum = Convert.ToInt32(reader["dhord#"]);
string billTo = reader["dhbcs#"].ToString().Trim();
string shipTo = reader["dhscs#"].ToString().Trim();
string currency = reader["dhcurr"].ToString().Trim();
string territory = reader["dhterr"].ToString().Trim();
int invoiceYear = Convert.ToInt32(reader["dharyr"]);
int invoicePeriod = Convert.ToInt32(reader["dharpr"]);
ExcoCustomer customer;
if (customerBillToMap.ContainsKey(billTo))
{
customer = customerBillToMap[billTo];
}
else if (customerShipToMap.ContainsKey(billTo))
{
customer = customerShipToMap[billTo];
}
else if (customerBillToMap.ContainsKey(shipTo))
{
customer = customerBillToMap[shipTo];
}
else if (customerShipToMap.ContainsKey(shipTo))
{
customer = customerShipToMap[shipTo];
}
else
{
throw new Exception("Unknown customer: " + billTo + " " + shipTo + " invoice#:" + invNum.ToString());
}
Invoice invoice = new Invoice();
invoice.orderNum = soNum;
invoice.invoiceNum = invNum;
invoice.customer = customer;
invoice.currency = currency;
invoice.territory = territory;
invoice.plant = plantID;
invoice.calendar = new ExcoCalendar(invoiceYear, invoicePeriod, true, plantID);
invoiceMap.Add(invNum, invoice);
}
reader.Close();
// get sales
query = "select diinv#, sum(dipric*diqtsp) as sale from cmsdat.oid where diglcd='SAL' group by diinv#";
reader = database.RunQuery(query);
while (reader.Read())
{
int invNum = Convert.ToInt32(reader[0]);
if (invoiceMap.ContainsKey(invNum))
{
invoiceMap[invNum].sale += Convert.ToDouble(reader
[1]);
}
}
reader.Close();
// get discount
query = "select flinv#, coalesce(sum(fldext), 0.0) from cmsdat.ois where fldisc like 'D%' or fldisc like 'M%' group by flinv#";
reader = database.RunQuery(query);
while (reader.Read())
{
int invNum = Convert.ToInt32(reader[0]);
if (invoiceMap.ContainsKey(invNum))
{
invoiceMap[invNum].discount += Convert.ToDouble(reader
[1]);
}
}
reader.Close();
// get fast track
query = "select flinv#, coalesce(sum(fldext), 0.0) from cmsdat.ois where fldisc like 'F%' group by flinv#";
reader = database.RunQuery(query);
while (reader.Read())
{
int invNum = Convert.ToInt32(reader[0]);
if (invoiceMap.ContainsKey(invNum))
{
invoiceMap[invNum].fastTrack += Convert.ToDouble(reader[1]);
}
}
reader.Close();
// get steel surcharge
query = "select flinv#, coalesce(sum(fldext), 0.0) from cmsdat.ois where fldisc like 'S%' or fldisc like 'P%' group by flinv#";
reader = database.RunQuery(query);
while (reader.Read())
{
int invNum = Convert.ToInt32(reader[0]);
if (invoiceMap.ContainsKey(invNum))
{
invoiceMap[invNum].surcharge += Convert.ToDouble(reader[1]);
}
}
reader.Close();
// get credit
query = "select dhinv#, coalesce(dipric*(max(diqtso,diqtsp)), 0.0) from cmsdat.oih, cmsdat.oid where dhincr='C' and dhpost='Y' and dhinv#=diinv# and diglcd='SAL'";
reader = database.RunQuery(query);
while (reader.Read())
{
int invNum = Convert.ToInt32(reader[0]);
if (invoiceMap.ContainsKey(invNum))
{
invoiceMap[invNum].credit += Convert.ToDouble(reader[1]);
}
}
reader.Close();
// get budgets
database.Open(Database.DECADE_MARKHAM);
switch (plantID)
{
case 1:
query = "select CustomerID, Currency, Period01, Period02, Period03, Period04, Period05, Period06, Period07, Period08, Period09, Period10, Period11, Period12 from tiger.dbo.Markham_Budget where Year=20" + fiscalYear.ToString();
break;
case 4:
query = "select CustomerID, Currency, Period01, Period02, Period03, Period04, Period05, Period06, Period07, Period08, Period09, Period10, Period11, Period12 from tiger.dbo.Colombia_Budget where Year=20" + fiscalYear.ToString();
break;
case 3:
query = "select CustomerID, Currency, Period01, Period02, Period03, Period04, Period05, Period06, Period07, Period08, Period09, Period10, Period11, Period12 from tiger.dbo.Michigan_Budget where Year=20" + fiscalYear.ToString();
break;
case 5:
query = "select CustomerID, Currency, Period01, Period02, Period03, Period04, Period05, Period06, Period07, Period08, Period09, Period10, Period11, Period12 from tiger.dbo.Texas_Budget where Year=20" + fiscalYear.ToString();
break;
}
reader = database.RunQuery(query);
while (reader.Read())
{
string currency = reader[1].ToString();
List <ExcoMoney> budgetList = new List <ExcoMoney>();
for (int i = 0; i < 12; i++)
{
ExcoMoney budget = new ExcoMoney(new ExcoCalendar(fiscalYear, i + 1, true, 1), Convert.ToDouble(reader[i + 2]), currency);
budgetList.Add(budget);
}
budgetMap.Add(reader[0].ToString().Trim(), budgetList);
}
reader.Close();
// get parts
database.Open(Database.CMSDAT);
query = "select diord#, dimajr, didesc, dipart from cmsdat.oid where distkl='" + plantID.ToString("D3") + "PRD'";
reader = database.RunQuery(query);
while (reader.Read())
{
int soNum = Convert.ToInt32(reader["diord#"]);
string part = reader["dipart"].ToString().Trim();
string desc = reader["didesc"].ToString().Trim();
string type = reader["dimajr"].ToString().Trim();
if (desc.Contains("NCR ") || part.Contains("NCR "))
{
type = "NCR";
}
if (partMap.ContainsKey(soNum))
{
partMap[soNum].Add(type);
}
else
{
List <string> typeList = new List <string>();
typeList.Add(type);
partMap.Add(soNum, typeList);
}
}
reader.Close();
// build plant data structure
foreach (ExcoCustomer customer in customerBillToMap.Values)
{
plant.AddCustomer(customer, invoiceMap, fiscalYear, doesIncludeSurcharge, budgetMap, partMap);
}
plant.GetSurcharge();
plant.CountParts();
}
/// <summary>
///
/// select a row from table t_RBSR_AUFW_u_Entitlement.
/// </summary>
/// <param name="ID"></param>
/// <returns>returnGetEntitlement</returns>
public returnGetEntitlement GetEntitlement(int ID)
{
returnGetEntitlement rv = new returnGetEntitlement();
DBConnect();
OdbcCommand cmd = _dbConnection.CreateCommand();
cmd.CommandText = "select \"c_id\",\"c_u_StandardActivity\",\"c_u_RoleType\",\"c_u_System\",\"c_u_Platform\",\"c_u_EntitlementName\",\"c_u_EntitlementValue\",\"c_u_AuthObjName\",\"c_u_AuthObjValue\",\"c_u_FieldSecName\",\"c_u_FieldSecValue\",\"c_u_Level4SecName\",\"c_u_Level4SecValue\",\"c_u_Commentary\",\"c_u_GENmanifestValue\",\"c_u_Application\",\"c_u_CHECKSUM\",\"c_u_Status\" from \"t_RBSR_AUFW_u_Entitlement\" where \"c_id\"= ?";
cmd.Parameters.Add("c_id", OdbcType.Int);
cmd.Parameters["c_id"].Value = (object)ID;
cmd.Connection = _dbConnection;
OdbcDataReader dr = cmd.ExecuteReader();
int drctr = 0;
while (dr.Read())
{
drctr++;
if (dr.IsDBNull(0))
{
throw new Exception("Value 'null' is not allowed for 'ID'");
}
else
{
rv.ID = dr.GetInt32(0);
}
if (dr.IsDBNull(1))
{
throw new Exception("Value 'null' is not allowed for 'StandardActivity'");
}
else
{
rv.StandardActivity = dr.GetString(1);
}
if (dr.IsDBNull(2))
{
throw new Exception("Value 'null' is not allowed for 'RoleType'");
}
else
{
rv.RoleType = dr.GetString(2);
}
if (dr.IsDBNull(3))
{
throw new Exception("Value 'null' is not allowed for 'System'");
}
else
{
rv.System = dr.GetString(3);
}
if (dr.IsDBNull(4))
{
throw new Exception("Value 'null' is not allowed for 'Platform'");
}
else
{
rv.Platform = dr.GetString(4);
}
if (dr.IsDBNull(5))
{
throw new Exception("Value 'null' is not allowed for 'EntitlementName'");
}
else
{
rv.EntitlementName = dr.GetString(5);
}
if (dr.IsDBNull(6))
{
throw new Exception("Value 'null' is not allowed for 'EntitlementValue'");
}
else
{
rv.EntitlementValue = dr.GetString(6);
}
if (dr.IsDBNull(7))
{
rv.AuthObjName = null;
}
else
{
rv.AuthObjName = dr.GetString(7);
}
if (dr.IsDBNull(8))
{
rv.AuthObjValue = null;
}
else
{
rv.AuthObjValue = dr.GetString(8);
}
if (dr.IsDBNull(9))
{
rv.FieldSecName = null;
}
else
{
rv.FieldSecName = dr.GetString(9);
}
if (dr.IsDBNull(10))
{
rv.FieldSecValue = null;
}
else
{
rv.FieldSecValue = dr.GetString(10);
}
if (dr.IsDBNull(11))
{
rv.Level4SecName = null;
}
else
{
rv.Level4SecName = dr.GetString(11);
}
if (dr.IsDBNull(12))
{
rv.Level4SecValue = null;
}
else
{
rv.Level4SecValue = dr.GetString(12);
}
if (dr.IsDBNull(13))
{
rv.Commentary = null;
}
else
{
rv.Commentary = dr.GetString(13);
}
if (dr.IsDBNull(14))
{
rv.GENmanifestValue = null;
}
else
{
rv.GENmanifestValue = dr.GetString(14);
}
if (dr.IsDBNull(15))
{
throw new Exception("Value 'null' is not allowed for 'Application'");
}
else
{
rv.Application = dr.GetString(15);
}
if (dr.IsDBNull(16))
{
throw new Exception("Value 'null' is not allowed for 'CHECKSUM'");
}
else
{
rv.CHECKSUM = dr.GetString(16);
}
if (dr.IsDBNull(17))
{
rv.Status = null;
}
else
{
rv.Status = dr.GetString(17);
}
}
dr.Close();
dr.Dispose();
if (drctr != 1)
{
throw new Exception("Operation selected no rows!");
}
cmd.Dispose();
DBClose();
return(rv);
}
protected void drp_by_SelectedIndexChanged(object sender, EventArgs e)
{
string by = drp_by.SelectedItem.Text;
if (by == "All Complaints")
{
lbl_2.Visible = false;
txt_val.Visible = false;
OdbcCommand cmd = conn_asset.CreateCommand();
cmd.CommandText = "select p_no,astCode,openingDate,closingDate,userDescription,systemDescription,partRepaired from ast_call order by openingDate desc";
conn_asset.Open();
cmd.CommandType = CommandType.Text;
DataTable dt = new DataTable();
DataRow newRow;
OdbcDataReader dr = cmd.ExecuteReader();
dt.Columns.Add(new System.Data.DataColumn("astCode", typeof(String)));
dt.Columns.Add(new System.Data.DataColumn("p_no", typeof(String)));
dt.Columns.Add(new System.Data.DataColumn("openingDate", typeof(DateTime)));
dt.Columns.Add(new System.Data.DataColumn("closingDate", typeof(DateTime)));
dt.Columns.Add(new System.Data.DataColumn("userDescription", typeof(String)));
dt.Columns.Add(new System.Data.DataColumn("systemDescription", typeof(String)));
dt.Columns.Add(new System.Data.DataColumn("partRepaired", typeof(String)));
while (dr.Read())
{
newRow = dt.NewRow();
newRow["astCode"] = Convert.ToString(dr["astCode"]);
newRow["p_no"] = Convert.ToString(dr["p_no"]);
newRow["openingDate"] = Convert.ToDateTime(dr["openingDate"]);
newRow["closingDate"] = Convert.ToDateTime(dr["closingDate"]);
newRow["userDescription"] = Convert.ToString(dr["userDescription"]);
newRow["systemDescription"] = Convert.ToString(dr["systemDescription"]);
newRow["partRepaired"] = Convert.ToString(dr["partRepaired"]);
dt.Rows.Add(newRow);
}
if (dt.Rows.Count > 0)
{
grid_all.Visible = true;
grid_astcode.Visible = false;
grid_pno.Visible = false;
grid_all.DataSource = dt;
grid_all.DataBind();
lbl_no_recs.Visible = false;
}
else
{
grid_pno.Visible = false;
grid_astcode.Visible = false;
grid_all.Visible = false;
lbl_no_recs.Visible = true;
}
conn_asset.Close();
}
}
protected bool LoadTableRows()
{
bool isDone = false;
List <SpatialObject> addedRows = new List <SpatialObject>();
List <SpatialObject> updatedRows = new List <SpatialObject>();
List <SpatialObject> deletedRows = new List <SpatialObject>();
List <SpatialObject> currentRows = new List <SpatialObject>();
using (OdbcConnection connection = new OdbcConnection(this.ConnectionString))
{
using (OdbcCommand command = new OdbcCommand(this.FeatureLayer.SelectSQL, connection))
{
try
{
connection.Open();
using (OdbcDataReader dr = command.ExecuteReader())
{
if (dr.HasRows)
{
int count = 0;
while (dr.Read())
{
count++;
SpatialObject obj = new SpatialObject();
obj.uniqueId = ((int)this.UpdateMode == 4) ? Convert.ToString(dr.GetValue(0)) : count.ToString();
obj.x = ((int)this.UpdateMode < 3) ? Convert.ToDecimal(dr.GetValue(1)) : -1;
obj.y = ((int)this.UpdateMode < 3) ? Convert.ToDecimal(dr.GetValue(2)) : -1;
obj.fieldValues = new FieldValueList();
for (int i = 0; i < dr.FieldCount; i++)
{
obj.fieldValues.Add(dr.GetName(i), dr.GetFieldType(i), dr.GetValue(i));
}
currentRows.Add(obj);
}
}
if (isLoaded && loadedRows != null)
{
currentRows.ForEach(current =>
{
SpatialObject loaded = loadedRows.FirstOrDefault(existed => existed.uniqueId == current.uniqueId);
if (loaded == null)
{
addedRows.Add(current);
loadedRows.Add(current);
}
else if (!loaded.Equals(current, this.UpdateMode, true))
{
updatedRows.Add(loaded);
}
});
deletedRows = loadedRows.Where(existed => !currentRows.Any(current => existed.uniqueId == current.uniqueId)).ToList();
deletedRows.ForEach(item => loadedRows.Remove(item));
isDone = ApplyEdits(addedRows, updatedRows, deletedRows);
}
else
{
this.isLoaded = true;
loadedRows = currentRows.ToList();
isDone = ApplyEdits(currentRows, null, null);
}
}
}
catch (Exception ex)
{
isDone = true;
isErrorLogged = true;
if (logCallback != null)
{
logCallback(string.Format("Query {0} Table Error - {1}", this.FeatureLayer.Name, ex.Message), EventLogEntryType.Error);
}
}
if (connection.State == System.Data.ConnectionState.Open)
{
connection.Close();
}
}
}
return(isDone);
}
/// <summary>
/// When user clicks on OK, validate that new password
/// and confirm password are same. Update the passord for
/// the user if the userid and password are correct.
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void okButton_Click(object sender, EventArgs e)
{
if (userIDTextBox.Text.Length <= 0)
{
MessageBox.Show("Please enter a User ID", "Missing Userid"
, MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else if (passwordTextBox.Text.Length <= 0)
{
MessageBox.Show("Please enter password", "Missing password"
, MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else if (newPasswordTextBox.Text.Length <= 5)
{
MessageBox.Show("Please enter at least 5 characters for new password",
"New Password Too Short"
, MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else if (newPasswordTextBox.Text.CompareTo(confirmPasswordTextBox.Text) != 0)
{
MessageBox.Show("The new password and confirm new password" +
" does not match", "Confirm new password"
, MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else
{
//Establish connection with database using OdbcConnection class
OdbcConnection objectOdbcConnection = new OdbcConnection(CONNECTION_STRING);
objectOdbcConnection.Open();
//Create an OdbcCommand object using CreateCommand of the OdbcConnection object
OdbcCommand objectOdbcCommand = objectOdbcConnection.CreateCommand();
objectOdbcCommand.CommandText = "UPDATE USERS set password = ? "
+ " WHERE userId = ? and password= ?;"
+ "SELECT password FROM USERS WHERE userid=?";
objectOdbcCommand.Parameters.Add("newPasword", OdbcType.NVarChar).Value = newPasswordTextBox.Text;
objectOdbcCommand.Parameters.Add("userid", OdbcType.NVarChar).Value = userIDTextBox.Text;
objectOdbcCommand.Parameters.Add("pasword", OdbcType.NVarChar).Value = passwordTextBox.Text;
objectOdbcCommand.Parameters.Add("userid", OdbcType.NVarChar).Value = userIDTextBox.Text;
OdbcDataReader dbReader = objectOdbcCommand.ExecuteReader();
dbReader.Read();
if (dbReader.HasRows)
{
string newPassword = dbReader["password"].ToString();
if (newPassword.CompareTo(confirmPasswordTextBox.Text) == 0)
{
MessageBox.Show("Password changed successfully", "Password Changed",
MessageBoxButtons.OK, MessageBoxIcon.Information);
this.Close();
}
else
{
MessageBox.Show("Sorry, password was not changed successfully",
"Password Not Changed",
MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
dbReader.Close();
objectOdbcCommand.Dispose();
objectOdbcConnection.Close();
}
}
public void RunQuery(ODBC odbc, string _sqlQ)
{
string ConectStreng;
ConectStreng = "DSN=BZDSNT;UID=Z6SGJ;Pwd=xxxxxx";
using (var conn = new OdbcConnection(ConectStreng))
{
try
{
OdbcCommand cmd = new OdbcCommand(_sqlQ, conn);
cmd.CommandTimeout = 1000 * 60; // 5 minutter
conn.Open();
var schema = conn.GetSchema();
var datasource = conn.DataSource;
using (OdbcDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
//SqlResult res = new SqlResult();
//result.Add(res);
//Kolloner col = new Kolloner();
string value, value1, value2;
value = reader.GetValue(0).ToString();
value1 = reader.GetValue(1).ToString();
value2 = reader.GetValue(2).ToString();
dt.Rows.Add(new object[3] {
value, value1, value2
});
for (int i = 0; i < reader.FieldCount; i++)
{
value = reader.GetValue(i).ToString();
}
}
}
}
catch (OdbcException ex)
{
if (ex.Message.Contains("USERNAME AND"))
{
MessageBox.Show("Formentlig password fejl. Der indhentes nyt ved næste SQL opslag." + "\n" + ex.Message);
}
else
{
MessageBox.Show(ex.Message);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}
}
}
public void DeleteFromDB(int index)
{
OdbcCommand cmd0 = new OdbcCommand();
OdbcDataReader reader0 = null;
if (cnn.State == System.Data.ConnectionState.Closed)
{
AddLogString("Невозможно установить соединение с БД (Oracle, MySql)");
AddLogString(" выполнение алгоритма - прервано..");
cnn.Close();
return;
}
cmd0.Connection = cnn;
AddLogString(" Start>>");
cmd0.CommandText = "select count(id) from sys_tree21 where id_lsttbl in (select id from sys_tbllst where upper(table_name) like 'DA_V_LST_" + index.ToString() + "');";
try
{
reader0 = cmd0.ExecuteReader();
}
catch (Exception ex8)
{
AddLogString(" выполнение алгоритма - прервано.." + cmd0.CommandText);
return;
}
decimal id_cnt = 0;
if (reader0.HasRows)
{
while (reader0.Read())
{
id_cnt = reader0.GetDecimal(0);
}
}
reader0.Close();
if (id_cnt > 0)
{
AddLogString(" Сегмент описан в структуре БД ( sys_tree21 ). Удаление прервано. ");
return;
}
// Part 1
cmd0.CommandText = "drop public synonym DA_V_CAT_" + index.ToString() + " ;";
try { cmd0.ExecuteNonQuery(); } catch (Exception ex8) { AddLogString(" Ошибка выполнения = " + cmd0.CommandText); }
cmd0.CommandText = "drop public synonym DA_V_LST_" + index.ToString() + " ;";
try { cmd0.ExecuteNonQuery(); } catch (Exception ex8) { AddLogString(" Ошибка выполнения = " + cmd0.CommandText); }
cmd0.CommandText = "drop public synonym ARC_DA_V_LST_" + index.ToString() + " ;";
try { cmd0.ExecuteNonQuery(); } catch (Exception ex8) { AddLogString(" Ошибка выполнения = " + cmd0.CommandText); }
cmd0.CommandText = "drop view DA_V_CAT_" + index.ToString() + " ;";
try { cmd0.ExecuteNonQuery(); } catch (Exception ex8) { AddLogString(" Ошибка выполнения = " + cmd0.CommandText); }
cmd0.CommandText = "drop view DA_V_LST_" + index.ToString() + " ;";
try { cmd0.ExecuteNonQuery(); } catch (Exception ex8) { AddLogString(" Ошибка выполнения = " + cmd0.CommandText); }
cmd0.CommandText = "drop view ARC_DA_V_LST_" + index.ToString() + " ;";
try { cmd0.ExecuteNonQuery(); } catch (Exception ex8) { AddLogString(" Ошибка выполнения = " + cmd0.CommandText); }
// Part 2
cmd0.CommandText = "delete from sys_tbllnk where id_lsttbl in (select id from sys_tbllst t where table_name like 'DA_V_LST_" + index.ToString() + "'" +
" and not exists (select 1 from sys_tree21 where id_lsttbl = t.id));";
try { cmd0.ExecuteNonQuery(); } catch (Exception ex8) { AddLogString(" Ошибка выполнения = " + cmd0.CommandText); }
cmd0.CommandText = "delete from sys_tblref where id_tbl in (select id from sys_tbllst t where table_name like 'DA_V_LST_" + index.ToString() + "'" +
" and not exists (select 1 from sys_tree21 where id_lsttbl = t.id));";
try { cmd0.ExecuteNonQuery(); } catch (Exception ex8) { AddLogString(" Ошибка выполнения = " + cmd0.CommandText); }
cmd0.CommandText = "delete from sys_tblref where id_tbl in (select id from sys_tbllst t where table_name like 'DA_V_CAT_" + index.ToString() + "'" +
" and not exists (select 1 from sys_tree21 where id_lsttbl IN (SELECT id_lsttbl FROM sys_tabl_v WHERE ID = t.id)));";
try { cmd0.ExecuteNonQuery(); } catch (Exception ex8) { AddLogString(" Ошибка выполнения = " + cmd0.CommandText); }
// Part 3
cmd0.CommandText = "delete from ARC_SERVICES_TUNE where id_sprofile in (select id from arc_subsyst_profile " +
" where id_tbllst in (select id from sys_tbllst t where table_name like 'DA_V_LST_" + index.ToString() + "'" +
" AND not exists (select 1 from sys_tree21 where id_lsttbl = t.id))); ";
try { cmd0.ExecuteNonQuery(); } catch (Exception ex8) { AddLogString(" Ошибка выполнения = " + cmd0.CommandText); }
cmd0.CommandText = "delete from ARC_SERVICES_ACCESS where id_sprofile in (select id from arc_subsyst_profile " +
" where id_tbllst in (select id from sys_tbllst t where table_name like 'DA_V_LST_" + index.ToString() + "'" +
" AND not exists (select 1 from sys_tree21 where id_lsttbl = t.id))); ";
try { cmd0.ExecuteNonQuery(); } catch (Exception ex8) { AddLogString(" Ошибка выполнения = " + cmd0.CommandText); }
cmd0.CommandText = "delete from ARC_SERVICES_INFO where id_lsttbl in (select id from sys_tbllst t where table_name like 'DA_V_LST_" + index.ToString() + "'" +
" AND not exists (select 1 from sys_tree21 where id_lsttbl = t.id)); ";
try { cmd0.ExecuteNonQuery(); } catch (Exception ex8) { AddLogString(" Ошибка выполнения = " + cmd0.CommandText); }
cmd0.CommandText = "delete from arc_subsyst_profile where id_tbllst in (select id from sys_tbllst t where table_name like 'DA_V_LST_" + index.ToString() + "'" +
" and not exists (select 1 from sys_tree21 where id_lsttbl = t.id)); ";
try { cmd0.ExecuteNonQuery(); } catch (Exception ex8) { AddLogString(" Ошибка выполнения = " + cmd0.CommandText); }
cmd0.CommandText = "delete from sys_app_serv_lst where id_lsttbl in (select id from sys_tbllst t where table_name like 'DA_V_LST_" + index.ToString() + "'" +
" and not exists (select 1 from sys_tree21 where id_lsttbl = t.id)); ";
try { cmd0.ExecuteNonQuery(); } catch (Exception ex8) { AddLogString(" Ошибка выполнения = " + cmd0.CommandText); }
cmd0.CommandText = "delete from ARC_READ_DEFAULTS where id_tbllst in (select id from sys_tbllst t where table_name like '%DA_V_LST_" + index.ToString() + "'" +
" and not exists (select 1 from sys_tree21 where id_lsttbl = t.id)); ";
try { cmd0.ExecuteNonQuery(); } catch (Exception ex8) { AddLogString(" Ошибка выполнения = " + cmd0.CommandText); }
cmd0.CommandText = "delete from ARC_READ_DEFAULTS where id_tbllst in (select id from sys_tbllst t where table_name like 'ARC_DA_V_LST_" + index.ToString() + "'" +
" and not exists (select 1 from sys_tree21 where id_lsttbl = t.id)); ";
try { cmd0.ExecuteNonQuery(); } catch (Exception ex8) { AddLogString(" Ошибка выполнения = " + cmd0.CommandText); }
cmd0.CommandText = "delete from ARC_READ_VIEW where id_tbllst in (select id from sys_tbllst t where table_name like 'DA_V_LST_" + index.ToString() + "'" +
" and not exists (select 1 from sys_tree21 where id_lsttbl = t.id)); ";
try { cmd0.ExecuteNonQuery(); } catch (Exception ex8) { AddLogString(" Ошибка выполнения = " + cmd0.CommandText); }
cmd0.CommandText = "delete from ad_sinfo where id_lsttbl in (select id from sys_tbllst t where table_name like 'DA_V_CAT_" + index.ToString() + "'" +
" and not exists (select 1 from sys_tree21 where id_lsttbl = t.id)); ";
try { cmd0.ExecuteNonQuery(); } catch (Exception ex8) { AddLogString(" Ошибка выполнения = " + cmd0.CommandText); }
// Part 5
decimal nID = 0;
string tname = "'DA_V_LST_" + index.ToString() + "'";
cmd0.CommandText = "select count(id) from sys_tree21 where id_lsttbl in (select id from sys_tbllst where table_name like " + tname + ");";
try { reader0 = cmd0.ExecuteReader(); }
catch (Exception ex7)
{
AddLogString(" выполнение алгоритма - прервано.." + cmd0.CommandText);
return;
}
if (reader0.HasRows)
{
while (reader0.Read())
{
nID = reader0.GetDecimal(0);
}
}
reader0.Close();
if (nID == 0)
{
cmd0.CommandText = "select id_node from sys_tbllst where table_name like " + tname + " ; ";
try { reader0 = cmd0.ExecuteReader(); }
catch (Exception ex7)
{
AddLogString(" выполнение алгоритма - прервано.." + cmd0.CommandText);
return;
}
if (reader0.HasRows)
{
while (reader0.Read())
{
nID = reader0.GetDecimal(0);
}
}
reader0.Close();
cmd0.CommandText = "delete from sys_tblref where id_tbl in (select id from sys_tbllst where id_node = " + nID.ToString() + ")";
try { cmd0.ExecuteNonQuery(); } catch (Exception ex8) { AddLogString(" Ошибка выполнения = " + cmd0.CommandText); }
cmd0.CommandText = "delete from sys_tbllnk where id_lsttbl in (select id from sys_tbllst where id_node = " + nID.ToString() + ")";
try { cmd0.ExecuteNonQuery(); } catch (Exception ex8) { AddLogString(" Ошибка выполнения = " + cmd0.CommandText); }
cmd0.CommandText = "delete from sys_tbllnk where id_dsttbl in (select id from sys_tbllst where id_node = " + nID.ToString() + ")";
try { cmd0.ExecuteNonQuery(); } catch (Exception ex8) { AddLogString(" Ошибка выполнения = " + cmd0.CommandText); }
cmd0.CommandText = "delete from sys_tbllst where id_node = " + nID.ToString();
try { cmd0.ExecuteNonQuery(); } catch (Exception ex8) { AddLogString(" Ошибка выполнения = " + cmd0.CommandText); }
cmd0.CommandText = "delete from sys_tbllst where table_name like " + tname;
try { cmd0.ExecuteNonQuery(); } catch (Exception ex8) { AddLogString(" Ошибка выполнения = " + cmd0.CommandText); }
cmd0.CommandText = "delete from sys_db_part where id = " + nID.ToString();
try { cmd0.ExecuteNonQuery(); } catch (Exception ex8) { AddLogString(" Ошибка выполнения = " + cmd0.CommandText); }
AddLogString(" Удалено для " + tname);
}
else
{
AddLogString(" НЕ удалено для " + tname + ": используется в Навигаторе!");
}
// Part 6
nID = 0;
tname = "'ARC_DA_V_LST_" + index.ToString() + "'";
cmd0.CommandText = "select count(id) from sys_tree21 where id_lsttbl in (select id from sys_tbllst where table_name like " + tname + ");";
try { reader0 = cmd0.ExecuteReader(); }
catch (Exception ex7)
{
AddLogString(" выполнение алгоритма - прервано.." + cmd0.CommandText);
return;
}
if (reader0.HasRows)
{
while (reader0.Read())
{
nID = reader0.GetDecimal(0);
}
}
reader0.Close();
if (nID == 0)
{
cmd0.CommandText = "select id_node from sys_tbllst where table_name like " + tname + " ; ";
try { reader0 = cmd0.ExecuteReader(); }
catch (Exception ex7)
{
AddLogString(" выполнение алгоритма - прервано.." + cmd0.CommandText);
return;
}
if (reader0.HasRows)
{
while (reader0.Read())
{
nID = reader0.GetDecimal(0);
}
}
reader0.Close();
cmd0.CommandText = "delete from sys_tblref where id_tbl in (select id from sys_tbllst where id_node = " + nID.ToString() + ")";
try { cmd0.ExecuteNonQuery(); } catch (Exception ex8) { AddLogString(" Ошибка выполнения = " + cmd0.CommandText); }
cmd0.CommandText = "delete from sys_tbllnk where id_lsttbl in (select id from sys_tbllst where id_node = " + nID.ToString() + ")";
try { cmd0.ExecuteNonQuery(); } catch (Exception ex8) { AddLogString(" Ошибка выполнения = " + cmd0.CommandText); }
cmd0.CommandText = "delete from sys_tbllnk where id_dsttbl in (select id from sys_tbllst where id_node = " + nID.ToString() + ")";
try { cmd0.ExecuteNonQuery(); } catch (Exception ex8) { AddLogString(" Ошибка выполнения = " + cmd0.CommandText); }
cmd0.CommandText = "delete from sys_tbllst where id_node = " + nID.ToString();
try { cmd0.ExecuteNonQuery(); } catch (Exception ex8) { AddLogString(" Ошибка выполнения = " + cmd0.CommandText); }
cmd0.CommandText = "delete from sys_tbllst where table_name like " + tname;
try { cmd0.ExecuteNonQuery(); } catch (Exception ex8) { AddLogString(" Ошибка выполнения = " + cmd0.CommandText); }
cmd0.CommandText = "delete from sys_db_part where id = " + nID.ToString();
try { cmd0.ExecuteNonQuery(); } catch (Exception ex8) { AddLogString(" Ошибка выполнения = " + cmd0.CommandText); }
AddLogString(" Удалено для " + tname);
}
else
{
AddLogString(" НЕ удалено для " + tname + ": используется в Навигаторе!");
}
AddLogString(" Ready>>");
return;
}
public DataSet GetFormDataP2010(string strPage)
{
string strQuery = "";
OdbcCommand cmdOdbc = new OdbcCommand();
OdbcDataAdapter daOdbc;
DataTable dtTemp = new DataTable();
DataSet dsData = new DataSet();
try
{
if (connOdbc.State == ConnectionState.Closed)
{
connOdbc.Open();
}
switch (strPage)
{
#region "Page1"
case "P2010Page1":
//strQuery= "SELECT PT_NAME, PT_REF_NO, " &
strQuery = "SELECT PT_NAME, PT_REF_NO, " +
"PT_REGISTER_NO, PT_NO_PARTNERS, PT_APPORTIONMENT, PT_COMPLIANCE, " +
"PT_REG_ADDRESS1 , PT_REG_ADDRESS2 ,PT_REG_ADDRESS3,PT_REG_POSTCODE,PT_REG_CITY,PT_REG_STATE," +
"PT_BUS_ADDRESS1 , PT_BUS_ADDRESS2 , PT_BUS_ADDRESS3 ,PT_BUS_POSTCODE,PT_BUS_CITY,PT_BUS_STATE," +
"PT_COR_ADDRESS1 , PT_COR_ADDRESS2 , PT_COR_ADDRESS3 ,PT_COR_POSTCODE, PT_COR_CITY,PT_COR_STATE," +
"PT_ACC_ADDRESS1 ,PT_ACC_ADDRESS2, PT_ACC_ADDRESS3,PT_ACC_POSTCODE, PT_ACC_CITY,PT_ACC_STATE," +
"PT_EMPLOYER_NO2,PT_PRE_PARTNER,PT_TEL1 + PT_TEL2,PT_MOBILE1 + PT_MOBILE2,PT_EMAIL, PT_BWA " +
"FROM TAXP_PROFILE where PT_REF_NO=?";
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer.ToString()));
daOdbc = new OdbcDataAdapter(cmdOdbc);
dtTemp = new DataTable("P1_INPUT_TAX_PROFILE");
daOdbc.Fill(dtTemp);
dsData.Tables.Add(dtTemp);
daOdbc.Dispose();
cmdOdbc.Dispose();
break;
#endregion
#region "Page2"
case "P2010Page2": //Pendapatan Perniagaan
strQuery = "SELECT [P_KEY] FROM [P_BALANCE_SHEET] WHERE [P_REF_NO]=? AND [P_YA]=? ";
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer));
cmdOdbc.Parameters.Add(new OdbcParameter("@ya", strYA));
OdbcDataReader drP = cmdOdbc.ExecuteReader();
string strTemp = "";
if (drP.HasRows)
{
while (drP.Read())
{
strTemp = drP.GetString(0);
}
}
//drP.Dispose;
//cmdOdbc.Dispose();
strQuery = "SELECT PI_TYPE,PI_PIONEER_INCOME,PI_INCOME_LOSS,PI_P_BEBEFIT,PI_BAL_CHARGE,PI_BAL_ALLOWANCE,PI_7A_ALLOWANCE,PI_EXP_ALLOWANCE " +
"FROM [P_BUSINESS_INCOME] WHERE [P_KEY] = ? and [PI_TYPE]='Yes'";
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
//cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer));
// cmdOdbc.Parameters.Add(new OdbcParameter("@ya", strYA));
cmdOdbc.Parameters.Add(new OdbcParameter("@KEY", strTemp));
daOdbc = new OdbcDataAdapter(cmdOdbc);
dtTemp = new DataTable("P2_INPUTT_TAX_BUSINESS");
daOdbc.Fill(dtTemp);
dsData.Tables.Add(dtTemp);
daOdbc.Dispose();
cmdOdbc.Dispose();
strQuery = "SELECT PS_CODE FROM [TAXP_PSOURCE] WHERE [PS_REF_NO]=? and [PS_YA]=? ORDER BY [PS_SOURCENO]";
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer));
cmdOdbc.Parameters.Add(new OdbcParameter("@ya", strYA));
daOdbc = new OdbcDataAdapter(cmdOdbc);
dtTemp = new DataTable("P2_SELECT_TAXP");
daOdbc.Fill(dtTemp);
dsData.Tables.Add(dtTemp);
daOdbc.Dispose();
cmdOdbc.Dispose();
strQuery = "SELECT Top 1 PI_REF_NO,PI_INCOME_LOSS,PI_BAL_CHARGE,PI_BAL_ALLOWANCE,PI_7A_ALLOWANCE,PI_EXP_ALLOWANCE,PI_SOURCENO,PI_PIONEER_INCOME FROM [P_BUSINESS_INCOME] WHERE [P_KEY] = ? and [PI_REF_NO]=? and [PI_TYPE]<>'Yes'";
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
cmdOdbc.Parameters.Add(new OdbcParameter("@KEY", strTemp));
cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer));
daOdbc = new OdbcDataAdapter(cmdOdbc);
dtTemp = new DataTable("P2_INPUTT_TAX_BUSINESS1");
daOdbc.Fill(dtTemp);
dsData.Tables.Add(dtTemp);
daOdbc.Dispose();
cmdOdbc.Dispose();
strQuery = "SELECT Top 1 PI_REF_NO,PI_INCOME_LOSS,PI_BAL_CHARGE,PI_BAL_ALLOWANCE,PI_7A_ALLOWANCE,PI_EXP_ALLOWANCE,PI_SOURCENO,PI_PIONEER_INCOME FROM [P_BUSINESS_INCOME] WHERE [P_KEY] = ? and [PI_REF_NO]<>? and [PI_TYPE]<>'Yes'";
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
cmdOdbc.Parameters.Add(new OdbcParameter("@KEY", strTemp));
cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer));
daOdbc = new OdbcDataAdapter(cmdOdbc);
dtTemp = new DataTable("P2_INPUTT_TAX_BUSINESS2");
daOdbc.Fill(dtTemp);
dsData.Tables.Add(dtTemp);
daOdbc.Dispose();
cmdOdbc.Dispose();
strQuery = "SELECT Top 1 PI_REF_NO,PI_INCOME_LOSS,PI_BAL_CHARGE,PI_BAL_ALLOWANCE,PI_7A_ALLOWANCE,PI_EXP_ALLOWANCE,PI_SOURCENO,PI_PIONEER_INCOME FROM [P_BUSINESS_INCOME] WHERE [P_KEY] = ? and [PI_REF_NO]=? and [PI_TYPE]<>'Yes'";
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
cmdOdbc.Parameters.Add(new OdbcParameter("@KEY", strTemp));
cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer));
OdbcDataReader dr = cmdOdbc.ExecuteReader();
string strTemp10 = "";
string strTemp11 = "";
if (dr.HasRows)
{
while (dr.Read())
{
strTemp10 = dr.GetString(6);
strTemp11 = dr.GetString(0);
}
}
cmdOdbc.Dispose();
if (strTemp10 != "")
{
strQuery = "SELECT PS_CODE FROM [TAXP_PSOURCE] WHERE [PS_REF_NO]=? and PS_YA=? and PS_SOURCENO=? ";
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer));
cmdOdbc.Parameters.Add(new OdbcParameter("@ya1", strYA));
cmdOdbc.Parameters.Add(new OdbcParameter("@Source", strTemp10));
daOdbc = new OdbcDataAdapter(cmdOdbc);
dtTemp = new DataTable("P2_SELECT_TAXP1");
daOdbc.Fill(dtTemp);
dsData.Tables.Add(dtTemp);
daOdbc.Dispose();
cmdOdbc.Dispose();
}
strQuery = "SELECT Top 1 PI_REF_NO,PI_INCOME_LOSS,PI_BAL_CHARGE,PI_BAL_ALLOWANCE,PI_7A_ALLOWANCE,PI_EXP_ALLOWANCE,PI_SOURCENO,PI_PIONEER_INCOME FROM [P_BUSINESS_INCOME] WHERE [P_KEY] = ? and [PI_REF_NO]<>? and [PI_TYPE]<>'Yes'";
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
cmdOdbc.Parameters.Add(new OdbcParameter("@KEY", strTemp));
cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer));
cmdOdbc.Parameters.Add(new OdbcParameter("@ya1", strYA));
OdbcDataReader dr2 = cmdOdbc.ExecuteReader();
string strTemp12 = "";
string strTemp13 = "";
if (dr2.HasRows)
{
while (dr2.Read())
{
strTemp12 = dr2.GetString(6);
strTemp13 = dr2.GetString(0);
}
}
cmdOdbc.Dispose();
if (strTemp12 != "")
{
strQuery = "SELECT PS_CODE FROM [TAXP_PSOURCE] WHERE [PS_REF_NO]=? and PS_YA=? and PS_SOURCENO=? ";
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTemp13));
cmdOdbc.Parameters.Add(new OdbcParameter("@ya1", strYA));
cmdOdbc.Parameters.Add(new OdbcParameter("@Source", strTemp12));
daOdbc = new OdbcDataAdapter(cmdOdbc);
dtTemp = new DataTable("P2_SELECT_TAXP2");
daOdbc.Fill(dtTemp);
dsData.Tables.Add(dtTemp);
daOdbc.Dispose();
cmdOdbc.Dispose();
}
break;
#endregion
#region "Page3"
case "P2010Page3": //Pendapatan Perniagaan
strQuery = "SELECT [P_KEY] FROM [P_BALANCE_SHEET] WHERE [P_REF_NO]=? AND P_YA=? ";
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer));
cmdOdbc.Parameters.Add(new OdbcParameter("@ya", strYA));
OdbcDataReader drP3 = cmdOdbc.ExecuteReader();
string strTempP3 = "";
if (drP3.HasRows)
{
while (drP3.Read())
{
strTempP3 = drP3.GetString(0);
}
}
////empty A8-A14
strQuery = "SELECT PI_REF_NO,PI_INCOME_LOSS,PI_BAL_CHARGE,PI_BAL_ALLOWANCE,PI_7A_ALLOWANCE,PI_EXP_ALLOWANCE,PI_SOURCENO,PI_PIONEER_INCOME " +
"FROM [P_BUSINESS_INCOME] WHERE [P_KEY] = ? and [PI_SOURCENO]=3 and [PI_TYPE]<>'Yes'";
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
cmdOdbc.Parameters.Add(new OdbcParameter("@KEY", strTempP3));
daOdbc = new OdbcDataAdapter(cmdOdbc);
dtTemp = new DataTable("P3_INPUTT_TAX_BUSINESS");
daOdbc.Fill(dtTemp);
dsData.Tables.Add(dtTemp);
daOdbc.Dispose();
cmdOdbc.Dispose();
//empty
strQuery = "SELECT PS_CODE FROM [TAXP_PSOURCE] WHERE [PS_REF_NO]=? and PS_YA=? and PS_SOURCENO=3 ";
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer));
cmdOdbc.Parameters.Add(new OdbcParameter("@ya", strYA));
daOdbc = new OdbcDataAdapter(cmdOdbc);
dtTemp = new DataTable("P3_SELECT_TAXP");
daOdbc.Fill(dtTemp);
dsData.Tables.Add(dtTemp);
daOdbc.Dispose();
cmdOdbc.Dispose();
////>S3 A8-A14
strQuery = "SELECT PI_REF_NO,PI_INCOME_LOSS,PI_BAL_CHARGE,PI_BAL_ALLOWANCE,PI_7A_ALLOWANCE,PI_EXP_ALLOWANCE,PI_SOURCENO,PI_PIONEER_INCOME " +
"FROM [P_BUSINESS_INCOME] WHERE [P_KEY] = ? and [PI_SOURCENO]>=3 and [PI_TYPE]<>'Yes'";
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
cmdOdbc.Parameters.Add(new OdbcParameter("@KEY", strTempP3));
daOdbc = new OdbcDataAdapter(cmdOdbc);
dtTemp = new DataTable("P3_INPUTT_TAX_BUSINESS1");
daOdbc.Fill(dtTemp);
dsData.Tables.Add(dtTemp);
daOdbc.Dispose();
cmdOdbc.Dispose();
//>S3
strQuery = "SELECT PS_CODE FROM [TAXP_PSOURCE] WHERE [PS_REF_NO]=? and PS_YA=? and PS_SOURCENO>=3 ";
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer));
cmdOdbc.Parameters.Add(new OdbcParameter("@ya", strYA));
daOdbc = new OdbcDataAdapter(cmdOdbc);
dtTemp = new DataTable("P3_SELECT_TAXP1");
daOdbc.Fill(dtTemp);
dsData.Tables.Add(dtTemp);
daOdbc.Dispose();
cmdOdbc.Dispose();
break;
#endregion
#region "Comment it for next project"
//#region "Page2"
//case "P2009Page2": //Pendapatan Perniagaan
// strQuery = "SELECT [P_KEY] FROM [P_BALANCE_SHEET] WHERE [P_REF_NO]=? AND P_YA=?";
// cmdOdbc = new OdbcCommand(strQuery, connOdbc);
// cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer));
// cmdOdbc.Parameters.Add(new OdbcParameter("@ya", strYA));
// OdbcDataReader drP = cmdOdbc.ExecuteReader();
// string strTemp = "";
// if (drP.HasRows)
// {
// while (drP.Read())
// { strTemp = drP.GetString(0);}
// }
// //drP.Dispose;
// //cmdOdbc.Dispose();
// strQuery = "SELECT Top 1 PI_REF_NO,PI_INCOME_LOSS, PI_P_BEBEFIT, PI_BAL_CHARGE,PI_BAL_ALLOWANCE,PI_7A_ALLOWANCE,PI_EXP_ALLOWANCE,PI_SOURCENO,PI_PIONEER_INCOME FROM [P_BUSINESS_INCOME] WHERE [P_KEY]=? and [PI_TYPE]='Yes'";
// cmdOdbc = new OdbcCommand(strQuery, connOdbc);
// //cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer));
// // cmdOdbc.Parameters.Add(new OdbcParameter("@ya", strYA));
// cmdOdbc.Parameters.Add(new OdbcParameter("@KEY", strTemp));
// daOdbc = new OdbcDataAdapter(cmdOdbc);
// dtTemp = new DataTable("P2_INPUTT_TAX_BUSINESS");
// daOdbc.Fill(dtTemp);
// dsData.Tables.Add(dtTemp);
// daOdbc.Dispose();
// cmdOdbc.Dispose();
// strQuery = "SELECT TOP 1 PI_SOURCENO FROM [P_BUSINESS_INCOME] WHERE [P_KEY] = ? and [PI_REF_NO] = (SELECT PI_REF_NO FROM [P_BUSINESS_INCOME] WHERE [P_KEY] = ? and [PI_TYPE]='Yes')";
// cmdOdbc = new OdbcCommand(strQuery, connOdbc);
// cmdOdbc.Parameters.Add(new OdbcParameter("@KEY", strTemp));
// cmdOdbc.Parameters.Add(new OdbcParameter("@KEY1", strTemp));
// daOdbc = new OdbcDataAdapter(cmdOdbc);
// OdbcDataReader dr = cmdOdbc.ExecuteReader();
// string strTemp6 = "";
// if (dr.HasRows)
// {
// while (dr.Read())
// { strTemp6 = dr.GetString(0); }
// }
// dr.Close();
// cmdOdbc.Dispose();
// if (strTemp6 != "")
// {
// strQuery = "SELECT PS_CODE FROM [TAXP_PSOURCE] WHERE [PS_REF_NO]=? and [PS_YA]=? AND [PS_SOURCENO]=?";
// cmdOdbc = new OdbcCommand(strQuery, connOdbc);
// cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer));
// cmdOdbc.Parameters.Add(new OdbcParameter("@ya", strYA));
// cmdOdbc.Parameters.Add(new OdbcParameter("@Source", strTemp6));
// daOdbc = new OdbcDataAdapter(cmdOdbc);
// dtTemp = new DataTable("P2_SELECT_TAXP");
// daOdbc.Fill(dtTemp);
// dsData.Tables.Add(dtTemp);
// daOdbc.Dispose();
// cmdOdbc.Dispose();
// }
// strQuery = "SELECT TOP 1 PI_REF_NO,PI_INCOME_LOSS,PI_BAL_CHARGE,PI_BAL_ALLOWANCE,PI_7A_ALLOWANCE,PI_EXP_ALLOWANCE,PI_SOURCENO,PI_PIONEER_INCOME FROM [P_BUSINESS_INCOME] WHERE [P_KEY] = ? and PI_REF_NO not in (SELECT PI_REF_NO FROM [P_BUSINESS_INCOME] WHERE [P_KEY] = ? and [PI_TYPE]='Yes') ORDER BY [PI_REF_NO], [PI_SOURCENO]";
// cmdOdbc = new OdbcCommand(strQuery, connOdbc);
// cmdOdbc.Parameters.Add(new OdbcParameter("@KEY", strTemp));
// cmdOdbc.Parameters.Add(new OdbcParameter("@KEY1", strTemp));
// daOdbc = new OdbcDataAdapter(cmdOdbc);
// dtTemp = new DataTable("P2_INPUTT_TAX_BUSINESS1");
// daOdbc.Fill(dtTemp);
// dsData.Tables.Add(dtTemp);
// daOdbc.Dispose();
// cmdOdbc.Dispose();
// strQuery = "SELECT Top 1 PI_SOURCENO, PI_REF_NO FROM [P_BUSINESS_INCOME] WHERE [P_KEY] = ? and PI_REF_NO not in (SELECT PI_REF_NO FROM [P_BUSINESS_INCOME] WHERE [P_KEY] = ? and [PI_TYPE]='Yes') ORDER BY [PI_REF_NO], [PI_SOURCENO]";
// cmdOdbc = new OdbcCommand(strQuery, connOdbc);
// cmdOdbc.Parameters.Add(new OdbcParameter("@KEY", strTemp));
// cmdOdbc.Parameters.Add(new OdbcParameter("@KEY1", strTemp));
// OdbcDataReader dr2 = cmdOdbc.ExecuteReader();
// string strTemp1 = "";
// string strTemp7 = "";
// if (dr2.HasRows)
// {
// while (dr2.Read())
// {
// strTemp1 = dr2.GetString(0);
// strTemp7 = dr2.GetString(1);
// }
// }
// dr2.Close();
// cmdOdbc.Dispose();
// if (strTemp1 != "" && strTemp7 != "")
// {
// strQuery = "SELECT PS_CODE FROM [TAXP_PSOURCE] WHERE [PS_REF_NO]=? and PS_YA=? and PS_SOURCENO=? ";
// cmdOdbc = new OdbcCommand(strQuery, connOdbc);
// cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTemp7));
// cmdOdbc.Parameters.Add(new OdbcParameter("@ya1", strYA));
// cmdOdbc.Parameters.Add(new OdbcParameter("@Source", strTemp1));
// daOdbc = new OdbcDataAdapter(cmdOdbc);
// dtTemp = new DataTable("P2_SELECT_TAXP1");
// daOdbc.Fill(dtTemp);
// dsData.Tables.Add(dtTemp);
// daOdbc.Dispose();
// cmdOdbc.Dispose();
// }
// break;
//#endregion
//#region "Page3"
//case "P2009Page3": //Pendapatan Perniagaan
// strQuery = "SELECT [P_KEY] FROM [P_BALANCE_SHEET] WHERE [P_REF_NO]=? AND P_YA=?";
// cmdOdbc = new OdbcCommand(strQuery, connOdbc);
// cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer));
// cmdOdbc.Parameters.Add(new OdbcParameter("@ya", strYA));
// OdbcDataReader drP3 = cmdOdbc.ExecuteReader();
// string strTempP3 = "";
// if (drP3.HasRows)
// {
// while (drP3.Read())
// { strTempP3 = drP3.GetString(0); }
// }
// ////empty A8-A14
// strQuery = "SELECT PI_REF_NO,PI_INCOME_LOSS, PI_P_BEBEFIT, PI_BAL_CHARGE,PI_BAL_ALLOWANCE,PI_7A_ALLOWANCE,PI_EXP_ALLOWANCE,PI_SOURCENO,PI_PIONEER_INCOME FROM [P_BUSINESS_INCOME] WHERE [P_KEY]=? and [PI_REF_NO]=" +
// "(SELECT [PI_REF_NO] FROM [P_BUSINESS_INCOME] WHERE [P_KEY]=? and [PI_TYPE]='Yes') and [PI_TYPE]='' ORDER BY [PI_REF_NO], [PI_SOURCENO]";
// cmdOdbc = new OdbcCommand(strQuery, connOdbc);
// cmdOdbc.Parameters.Add(new OdbcParameter("@KEY", strTempP3));
// cmdOdbc.Parameters.Add(new OdbcParameter("@KEY1", strTempP3));
// daOdbc = new OdbcDataAdapter(cmdOdbc);
// dtTemp = new DataTable("P3_INPUTT_TAX_BUSINESS");
// daOdbc.Fill(dtTemp);
// dsData.Tables.Add(dtTemp);
// daOdbc.Dispose();
// cmdOdbc.Dispose();
// strQuery = "SELECT PI_SOURCENO FROM [P_BUSINESS_INCOME] WHERE [P_KEY] = ? and PI_REF_NO not in (SELECT PI_REF_NO FROM [P_BUSINESS_INCOME] WHERE [P_KEY] = ? and [PI_TYPE]='Yes') ORDER BY [PI_REF_NO], [PI_SOURCENO]";
// cmdOdbc = new OdbcCommand(strQuery, connOdbc);
// cmdOdbc.Parameters.Add(new OdbcParameter("@KEY", strTempP3));
// cmdOdbc.Parameters.Add(new OdbcParameter("@KEY1", strTempP3));
// OdbcDataReader dr3 = cmdOdbc.ExecuteReader();
// string strTemp8 = "";
// if (dr3.HasRows)
// {
// while (dr3.Read())
// {
// strTemp8 = dr3.GetString(0);
// }
// }
// dr3.Close();
// cmdOdbc.Dispose();
// //empty
// if (strTemp8 != "")
// {
// strQuery = "SELECT PS_CODE FROM [TAXP_PSOURCE] WHERE [PS_REF_NO]=? and PS_YA=? and [PS_SOURCENO]=?";
// cmdOdbc = new OdbcCommand(strQuery, connOdbc);
// cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer));
// cmdOdbc.Parameters.Add(new OdbcParameter("@ya", strYA));
// cmdOdbc.Parameters.Add(new OdbcParameter("@sourceno", strTemp8));
// daOdbc = new OdbcDataAdapter(cmdOdbc);
// dtTemp = new DataTable("P3_SELECT_TAXP");
// daOdbc.Fill(dtTemp);
// dsData.Tables.Add(dtTemp);
// daOdbc.Dispose();
// cmdOdbc.Dispose();
// }
// ////>S3 A8-A14
// strQuery = "SELECT PI_REF_NO,PI_INCOME_LOSS,PI_BAL_CHARGE,PI_BAL_ALLOWANCE,PI_7A_ALLOWANCE,PI_EXP_ALLOWANCE,PI_SOURCENO,PI_PIONEER_INCOME " +
// "FROM [P_BUSINESS_INCOME] WHERE [P_KEY] = ? and [PI_REF_NO] not in (SELECT [PI_REF_NO] FROM [P_BUSINESS_INCOME] WHERE [P_KEY] = ? and [PI_TYPE]='Yes') " +
// "ORDER BY [PI_REF_NO], [PI_SOURCENO]";
// cmdOdbc = new OdbcCommand(strQuery, connOdbc);
// cmdOdbc.Parameters.Add(new OdbcParameter("@KEY", strTempP3));
// cmdOdbc.Parameters.Add(new OdbcParameter("@KEY1", strTempP3));
// daOdbc = new OdbcDataAdapter(cmdOdbc);
// dtTemp = new DataTable("P3_INPUTT_TAX_BUSINESS1");
// daOdbc.Fill(dtTemp);
// dsData.Tables.Add(dtTemp);
// daOdbc.Dispose();
// cmdOdbc.Dispose();
// strQuery = "SELECT PI_SOURCENO, PI_REF_NO FROM [P_BUSINESS_INCOME] WHERE [P_KEY] = ? and PI_REF_NO not in (SELECT PI_REF_NO FROM [P_BUSINESS_INCOME] WHERE [P_KEY] = ? and [PI_TYPE]='Yes') ORDER BY [PI_REF_NO], [PI_SOURCENO]";
// cmdOdbc = new OdbcCommand(strQuery, connOdbc);
// cmdOdbc.Parameters.Add(new OdbcParameter("@KEY", strTempP3));
// cmdOdbc.Parameters.Add(new OdbcParameter("@KEY1", strTempP3));
// OdbcDataReader dr4 = cmdOdbc.ExecuteReader();
// string strTemp9 = "";
// string strTemp10 = "";
// if (dr4.HasRows)
// {
// while (dr4.Read())
// {
// strTemp9 = dr4.GetString(0);
// strTemp10 = dr4.GetString(1);
// }
// }
// dr4.Close();
// cmdOdbc.Dispose();
// //>S3
// if (strTemp9 != "" && strTemp10 != "")
// {
// strQuery = "SELECT PS_CODE FROM [TAXP_PSOURCE] WHERE [PS_REF_NO]=? and PS_YA=? and PS_SOURCENO=?";
// cmdOdbc = new OdbcCommand(strQuery, connOdbc);
// cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTemp10));
// cmdOdbc.Parameters.Add(new OdbcParameter("@ya", strYA));
// cmdOdbc.Parameters.Add(new OdbcParameter("@source", strTemp9));
// daOdbc = new OdbcDataAdapter(cmdOdbc);
// dtTemp = new DataTable("P3_SELECT_TAXP1");
// daOdbc.Fill(dtTemp);
// dsData.Tables.Add(dtTemp);
// daOdbc.Dispose();
// cmdOdbc.Dispose();
// }
// break;
//#endregion
#endregion
#region "Page4"
case "P2010Page4":
strQuery = "SELECT P_DIVISIBLE_INT_DIS,P_DIVISIBLE_RENT_ROY_PRE,P_DIVISIBLE_NOTLISTED,P_DIVISIBLE_ADD_43,P_TAXDED_110," +
" P_TAXDED_132,P_TAXDED_133,P_DIVISIBLE_ADD_43 FROM [PARTNERSHIP_INCOME] WHERE P_REF_NO=? AND P_YA=?";
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer));
cmdOdbc.Parameters.Add(new OdbcParameter("@ya", strYA));
daOdbc = new OdbcDataAdapter(cmdOdbc);
dtTemp = new DataTable("P3_PARTNERSHIP_INCOME");
daOdbc.Fill(dtTemp);
dsData.Tables.Add(dtTemp);
daOdbc.Dispose();
cmdOdbc.Dispose();
strQuery = "SELECT [P_KEY] FROM [P_BALANCE_SHEET] WHERE [P_REF_NO]=? AND P_YA=? ";
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer));
cmdOdbc.Parameters.Add(new OdbcParameter("@ya", strYA));
OdbcDataReader dr1 = cmdOdbc.ExecuteReader();
string strTemp2 = "";
if (dr1.HasRows)
{
while (dr1.Read())
{
strTemp2 = dr1.GetString(0);
}
}
//drP.Dispose;
cmdOdbc.Dispose();
strQuery = ("SELECT Top 10 PY_INCOME_TYPE,PY_PAYMENT_YEAR,PY_AMOUNT,PY_EPF FROM [PRECEDING_YEAR] WHERE [P_KEY] = ? order by [PY_DKEY]");
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
//cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer));
cmdOdbc.Parameters.Add(new OdbcParameter("@PKey", strTemp2));
daOdbc = new OdbcDataAdapter(cmdOdbc);
dtTemp = new DataTable("P3_PRECEDING_YEAR");
daOdbc.Fill(dtTemp);
dsData.Tables.Add(dtTemp);
daOdbc.Dispose();
cmdOdbc.Dispose();
strQuery = "SELECT P_DIV_MALDIV,P_TAX_MALDIV FROM [PARTNERSHIP_INCOME] WHERE P_REF_NO=? AND P_YA=?";
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer));
cmdOdbc.Parameters.Add(new OdbcParameter("@ya", strYA));
daOdbc = new OdbcDataAdapter(cmdOdbc);
dtTemp = new DataTable("P3_PARTNERSHIP_INCOME1");
daOdbc.Fill(dtTemp);
dsData.Tables.Add(dtTemp);
daOdbc.Dispose();
cmdOdbc.Dispose();
break;
#endregion
#region "Page5"
case "P2010Page5":
strQuery = "SELECT P_DIVS_EXP_1,P_DIVS_EXP_3,P_DIVS_EXP_4,P_DIVS_EXP_5,P_DIVS_EXP_8 FROM [PARTNERSHIP_INCOME] WHERE P_REF_NO=? AND P_YA=?";
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer));
cmdOdbc.Parameters.Add(new OdbcParameter("@ya", strYA));
daOdbc = new OdbcDataAdapter(cmdOdbc);
dtTemp = new DataTable("P4_PARTNERSHIP_INCOME");
daOdbc.Fill(dtTemp);
dsData.Tables.Add(dtTemp);
daOdbc.Dispose();
cmdOdbc.Dispose();
strQuery = "SELECT [P_KEY] FROM [P_BALANCE_SHEET] WHERE [P_REF_NO]=? AND P_YA=? ";
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer));
cmdOdbc.Parameters.Add(new OdbcParameter("@ya", strYA));
OdbcDataReader dr8 = cmdOdbc.ExecuteReader();
string strTemp3 = "";
if (dr8.HasRows)
{
while (dr8.Read())
{
strTemp3 = dr8.GetString(0);
}
}
dr8.Close();
cmdOdbc.Dispose();
strQuery = ("SELECT Top 10 PC_CL_CODE,PC_AMOUNT FROM [P_OTHER_CLAIMS] WHERE [P_KEY] = ? order by [PC_KEY]");
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
cmdOdbc.Parameters.Add(new OdbcParameter("@Key", strTemp3));
daOdbc = new OdbcDataAdapter(cmdOdbc);
dtTemp = new DataTable("P4_P_OTHER_CLAIMS");
daOdbc.Fill(dtTemp);
dsData.Tables.Add(dtTemp);
daOdbc.Dispose();
cmdOdbc.Dispose();
break;
#endregion
#region "Page6"
case "P2010Page6":
strQuery = "SELECT P_WITHTAX_107A_GROSS,P_WITHTAX_107A_TAX," +
" P_WITHTAX_109_GROSS, P_WITHTAX_109_TAX," +
" P_WITHTAX_109A_GROSS,P_WITHTAX_109A_TAX," +
" P_WITHTAX_109B_GROSS,P_WITHTAX_109B_TAX," +
" P_WITHTAX_109F_GROSS,P_WITHTAX_109F_TAX" +
" FROM [PARTNERSHIP_INCOME] WHERE P_REF_NO=? AND P_YA=?";
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer));
cmdOdbc.Parameters.Add(new OdbcParameter("@ya", strYA));
daOdbc = new OdbcDataAdapter(cmdOdbc);
dtTemp = new DataTable("P5_PARTNERSHIP_INCOME");
daOdbc.Fill(dtTemp);
dsData.Tables.Add(dtTemp);
daOdbc.Dispose();
cmdOdbc.Dispose();
strQuery = "SELECT [P_KEY] FROM [P_BALANCE_SHEET] WHERE [P_REF_NO]=? AND P_YA=? ";
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer));
cmdOdbc.Parameters.Add(new OdbcParameter("@ya", strYA));
OdbcDataReader dr6 = cmdOdbc.ExecuteReader();
string strTemp4 = "";
if (dr6.HasRows)
{
while (dr6.Read())
{
strTemp4 = dr6.GetString(0);
}
}
else
{
strTemp4 = "0";
}
// dr4.Dispose;
cmdOdbc.Dispose();
strQuery = "SELECT P_CP30_ASAL,format(P_CP30_ASAL_DATE,'dd/mm/yyyy'), P_CP30_PINDAAN, format(P_CP30_PINDAAN_DATE,'dd/mm/yyyy') FROM [PARTNERSHIP_INCOME] where [P_KEY]=?";
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
cmdOdbc.Parameters.Add(new OdbcParameter("@PKey", strTemp4));
daOdbc = new OdbcDataAdapter(cmdOdbc);
dtTemp = new DataTable("P5_PARTNERSHIP_INCOME1");
daOdbc.Fill(dtTemp);
dsData.Tables.Add(dtTemp);
daOdbc.Dispose();
cmdOdbc.Dispose();
break;
#endregion
#region "Page7"
case "P2010Page7":
strQuery = "SELECT [PT_KEY] FROM [TAXP_PROFILE] WHERE [PT_REF_NO]=? ";
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer));
OdbcDataReader dr5 = cmdOdbc.ExecuteReader();
string strTemp5 = "0";
if (dr5.HasRows)
{
while (dr5.Read())
{
strTemp5 = dr5.GetString(0);
}
}
cmdOdbc.Dispose();
dr5.Close();
strQuery = "SELECT [P_KEY] FROM [PARTNERSHIP_INCOME] WHERE [P_REF_NO]=? AND [P_YA]=?";
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer));
cmdOdbc.Parameters.Add(new OdbcParameter("@ya", strYA));
OdbcDataReader dr9 = cmdOdbc.ExecuteReader();
string strTemp9 = "0";
if (dr9.HasRows)
{
while (dr9.Read())
{
strTemp9 = dr9.GetString(0);
}
}
cmdOdbc.Dispose();
dr9.Close();
strQuery = "select PN_PREFIX As [PREFIX],PN_REF_NO AS [REFERENCE_NO]," +
"PN_NAME AS [NAME],PN_IDENTITY AS [IDENTITY],PN_COUNTRY AS [COUNTRY]," +
"PN_DATE_APPOINTNENT AS [DATE_APPOINTMENT]," +
"PN_DATE_CESSATION AS [DATE_CESSATION],PN_SHARE AS [SHARE], " +
"PN_BENEFIT_1 AS [BENEFIT_1],PN_BENEFIT_2 AS [BENEFIT_2],PN_BENEFIT_3 AS [BENEFIT_3]," +
"CP_B_ADJ_INCOMELOSS AS [ADJUSTED_AMOUNT], CP_B_BAL_CHARGE AS [BALANCING_CHARGE]," +
"CP_B_BAL_ALLOWANCE AS [BALANCING_ALLOWANCE], CP_B_7A_ALLOWANCE AS [SCHEDULE_7A]," +
"CP_B_EXP_ALLOWANCE AS [EXPORT_ALLOWANCE] " +
"from CP30 a INNER JOIN TAXP_PARTNERS b on a.CP_REF_NO = b.PN_REF_NO " +
"WHERE [PT_KEY] = ? AND [P_KEY] = ? order by PN_KEY";
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
cmdOdbc.Parameters.Add(new OdbcParameter("@PartnerKey", strTemp5));
cmdOdbc.Parameters.Add(new OdbcParameter("@Key", strTemp9));
daOdbc = new OdbcDataAdapter(cmdOdbc);
dtTemp = new DataTable("P6_TAXP_PARTNERS");
daOdbc.Fill(dtTemp);
dsData.Tables.Add(dtTemp);
daOdbc.Dispose();
cmdOdbc.Dispose();
strQuery = "SELECT [PT_PRE_PARTNER] FROM [TAXP_PROFILE] WHERE [PT_REF_NO]=? ";
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer));
daOdbc = new OdbcDataAdapter(cmdOdbc);
dtTemp = new DataTable("P6_TAXP_PREPARTNER");
daOdbc.Fill(dtTemp);
dsData.Tables.Add(dtTemp);
daOdbc.Dispose();
cmdOdbc.Dispose();
break;
#endregion
#region "Page8"
case "P2010Page8":
strQuery = "SELECT [PS_CODE] FROM [TAXP_PSOURCE] where PS_REF_NO=? and PS_YA=?";
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer));
cmdOdbc.Parameters.Add(new OdbcParameter("@ya", strYA));
daOdbc = new OdbcDataAdapter(cmdOdbc);
dtTemp = new DataTable("P7_TAXP_PSOURCE");
daOdbc.Fill(dtTemp);
dsData.Tables.Add(dtTemp);
daOdbc.Dispose();
cmdOdbc.Dispose();
strQuery = "SELECT PPL_SALES,PPL_OP_STK,PPL_PURCHASES_COST,PPL_CLS_STK, PPL_COGS," +
"PPL_OTH_BSIN,PPL_OTH_IN_DIVIDEND,PPL_OTH_IN_INTEREST,PPL_OTH_IN_RENTAL_ROYALTY,PPL_OTH_IN_OTHER," +
"PPL_EXP_LOANINTEREST,PPL_EXP_SALARY,PPL_EXP_RENTAL,PPL_EXP_CONTRACT,PPL_EXP_COMMISSION," +
"PPL_BAD_DEBTS,PPL_TRAVEL,PPL_EXP_REPAIR_MAINT,PPL_EXP_PRO_ADV,PPL_OTHER_EXP, " +
"PPL_NET_PROFIT,PPL_DISALLOWED_EXP FROM [P_PROFIT_AND_LOSS] where P_REF_NO=? and P_YA=?";
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer));
cmdOdbc.Parameters.Add(new OdbcParameter("@ya", strYA));
daOdbc = new OdbcDataAdapter(cmdOdbc);
dtTemp = new DataTable("P7_P_PROFIT_AND_LOSS");
daOdbc.Fill(dtTemp);
dsData.Tables.Add(dtTemp);
daOdbc.Dispose();
cmdOdbc.Dispose();
strQuery = "SELECT BS_LAND,BS_MACHINERY,BS_TRANSPORT,BS_OTH_FA," +
"BS_INVESTMENT, BS_STOCK,BS_TRADE_DEBTORS,BS_OTH_DEBTORS,BS_CASH,BS_BANK,BS_OTH_CA," +
"BS_LOAN, BS_TRADE_CR,BS_OTHER_CR,BS_OTH_LIAB,BS_LT_LIAB, " +
"BS_CAPITALACCOUNT,BS_BROUGHT_FORWARD,BS_CY_PROFITLOSS,BS_DRAWING, BS_CARRIED_FORWARD " +
"FROM [P_BALANCE_SHEET] where P_REF_NO=? and P_YA=? order by BS_SOURCENO";
cmdOdbc = new OdbcCommand(strQuery, connOdbc);
cmdOdbc.Parameters.Add(new OdbcParameter("@taxpayer", strTaxPayer));
cmdOdbc.Parameters.Add(new OdbcParameter("@ya", strYA));
daOdbc = new OdbcDataAdapter(cmdOdbc);
dtTemp = new DataTable("P7_P_PROFIT_AND_LOSS1");
daOdbc.Fill(dtTemp);
dsData.Tables.Add(dtTemp);
daOdbc.Dispose();
cmdOdbc.Dispose();
break;
#endregion
#region "Page9"
case "P2010Page9":
if (connOdbcB.State == ConnectionState.Closed)
{
connOdbcB.Open();
}
strQuery = "select ta_add_line1, ta_add_line2, ta_add_line3, ta_add_postcode, ta_add_city, ta_add_state, ta_tel_no, TA_MOBILE, ta_email from taxa_profile where ta_co_name=?";
cmdOdbc = new OdbcCommand(strQuery, connOdbcB);
cmdOdbc.Parameters.Add(new OdbcParameter("@taxagent", strTaxAgent));
daOdbc = new OdbcDataAdapter(cmdOdbc);
dtTemp = new DataTable("P8_FIRM");
daOdbc.Fill(dtTemp);
dsData.Tables.Add(dtTemp);
daOdbc.Dispose();
cmdOdbc.Dispose();
break;
#endregion
}
}
catch (Exception ex)
{ System.Windows.Forms.MessageBox.Show(ex.ToString()); }
return(dsData);
}
protected void deleteLocation(object sender, EventArgs e)
{
LabelError.Visible = false;
List <string> list = new List <string>();
//On récupère tous les ID vente sélectionné
foreach (GridViewRow row in GridView1.Rows)
{
System.Web.UI.WebControls.CheckBox check = (System.Web.UI.WebControls.CheckBox)row.FindControl("CheckBoxArchiver");
var selectedKey = GridView1.DataKeys[row.RowIndex].Value.ToString();
if (check.Checked)
{
if (!list.Contains(selectedKey))
{
list.Add(selectedKey);
}
}
}
if (list.Count < 1)
{
LabelError.Visible = true;
LabelError.Text = "veuillez sélectionnez au moins une vente à supprimer<br/><br/>";
}
//si il y a des ventes sélectionnées
if (list.Count > 0)
{
bool valid = true;
//on regarde si les proposition de vente ont été validés
foreach (string item in list)
{
string reqProposition = "SELECT Locations.valider_proposition FROM Locations WHERE Locations.ID=" + item;
OdbcConnection c = new OdbcConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
OdbcCommand requetteProposition = new OdbcCommand(reqProposition, c);
c.Open();
OdbcDataReader read = requetteProposition.ExecuteReader();
while (read.Read())
{
string validprop = read["valider_proposition"].ToString();
if (validprop == "True")
{
valid = false;
}
}
c.Close();
}
//si les propositions de location ne sont pas validés on supprime les locations
if (valid == true)
{
foreach (string item in list)
{
string reqLocation = "DELETE FROM Locations WHERE ID=" + item;
string reqHonoraire = "DELETE FROM Ventes_honoraires WHERE id_location=" + item;
OdbcConnection c = new OdbcConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
OdbcCommand requeteLocation = new OdbcCommand(reqLocation, c);
OdbcCommand requeteHonoraire = new OdbcCommand(reqHonoraire, c);
c.Open();
requeteLocation.ExecuteNonQuery();
requeteHonoraire.ExecuteNonQuery();
c.Close();
}
Response.Redirect(Request.RawUrl);
}
else
{
LabelError.Visible = true;
LabelError.Text = "Au moins une location sélectionnée à été validée par un administrateur et ne peut pas être supprimée<br/>Si vous souhaitez la supprimer, veuillez contacter un administrateur<br/><br/>";
}
}
}
private void button7_Click(object sender, EventArgs e)
{
string auxi;
ConexionCapaDatos cone = new ConexionCapaDatos();
int contador = 1;
double sueldoM;
double sueldoD;
string saldo;
try {
try {
int x = 1;
string[] vector = new string[1024];
string nuevo_emp = Microsoft.VisualBasic.Interaction.InputBox("Ingrese el nuevo id del empleado", "Ingreso");
string cadena = "select ID_Empleado from Percepciones";/* where ID_Empleado="+
* // nuevo_emp +" and ID_Percepcion=" +"'"+ID_PER+"'"+"; ";*/
OdbcCommand cmd = new OdbcCommand(cadena, cone.cnxOpen());
OdbcDataReader leer = cmd.ExecuteReader();
while (leer.Read())
{
vector[x] = leer.GetString(0);
x++;
}
cone.cnxClose();
for (int y = 1; y < x; y++)
{
string cadenaA = "select ID_Empleado from Percepciones where ID_Empleado=" +
nuevo_emp + " and ID_Percepcion=" + "'" + ID_PER + "'" + "; ";
OdbcCommand cmd1 = new OdbcCommand(cadenaA, cone.cnxOpen());
OdbcDataReader leer1 = cmd1.ExecuteReader();
while (leer1.Read())
{
if (vector[y] == nuevo_emp)
{
contador = 1;
}
else
{
contador = 0;
}
}
cone.cnxClose();
}
if (contador == 1)
{
MessageBox.Show("EMPLEADO YA INGRESADO");
}
else
{
string nuevo_dias = Microsoft.VisualBasic.Interaction.InputBox("Ingrese los dias", "Ingreso");
///////////////////
int diastxt = Convert.ToInt32(nuevo_dias);
string cadenaB = "SELECT tbl_empleados.ID_empleado ,tbl_empleados.nombre,tbl_contratos.salario " +
"FROM tbl_empleados " +
"INNER JOIN tbl_contratos ON tbl_empleados.ID_contrato = tbl_contratos.ID_contrato WHERE ID_Empleado=" + nuevo_emp + ";";
MessageBox.Show(cadenaB);
OdbcCommand cmd2 = new OdbcCommand(cadenaB, cone.cnxOpen());
OdbcDataReader leer1 = cmd2.ExecuteReader();
while (leer1.Read())
{
saldo = leer.GetString(2);
sueldo = Convert.ToDouble(saldo);
switch (mescmx)
{
case (1):
sueldoM = (sueldo / 31);
sueldoD = (sueldoM * diastxt);
sueldo = sueldoD + bono;
break;
case (2):
sueldoM = (sueldo / 28);
sueldoD = (sueldoM * diastxt);
sueldo = sueldoD + bono;
break;
case (3):
sueldoM = (sueldo / 31);
sueldoD = (sueldoM * diastxt);
sueldo = sueldoD + bono;
break;
case (4):
sueldoM = (sueldo / 30);
sueldoD = (sueldoM * diastxt);
sueldo = sueldoD + bono;
break;
case (5):
sueldoM = (sueldo / 31);
sueldoD = (sueldoM * diastxt);
sueldo = sueldoD + bono;
break;
case (6):
sueldoM = (sueldo / 30);
sueldoD = (sueldoM * diastxt);
sueldo = sueldoD + bono;
break;
case (7):
sueldoM = (sueldo / 31);
sueldoD = (sueldoM * diastxt);
sueldo = sueldoD + bono;
break;
case (8):
sueldoM = (sueldo / 31);
sueldoD = (sueldoM * diastxt);
sueldo = sueldoD + bono;
break;
case (9):
sueldoM = (sueldo / 30);
sueldoD = (sueldoM * diastxt);
sueldo = sueldoD + bono;
break;
case (10):
sueldoM = (sueldo / 31);
sueldoD = (sueldoM * diastxt);
sueldo = sueldoD + bono;
break;
case (11):
sueldoM = (sueldo / 30);
sueldoD = (sueldoM * diastxt);
sueldo = sueldoD + bono;
break;
case (12):
sueldoM = (sueldo / 31);
sueldoD = (sueldoM * diastxt);
sueldo = sueldoD + bono;
break;
}
}
auxi = String.Format("{0:0.00}", sueldo);
string result = auxi.Replace(",", ".");
DIA = textBox6.Text;
string cadenaC = "insert into Percepciones value(" +
"\"" + ID_PER + "\"," + ID_EMP + "," + result + "," + diastxt + ");";
OdbcCommand cmd3 = new OdbcCommand(cadena, cone.cnxOpen());
cmd.ExecuteNonQuery();
cone.cnxClose();
DataTable dt = new DataTable();
cadena = "SELECT ID_Empleado, ID_Percepcion, Total, Dias FROM Percepciones" +
" WHERE ID_Percepcion=" + "\"" + ID_PER + "\"" + ";";
OdbcDataAdapter dta = new OdbcDataAdapter(cadena, cone.cnxOpen());
DataSet dst = new DataSet();
dta.Fill(dst);
dt = dst.Tables[0];
dataGridView1.DataSource = dt;
MessageBox.Show("EMPLEADO INSERTADO");
cone.cnxClose();
////////////////
}
}
catch (OdbcException ex)
{
MessageBox.Show("Error en la base de datos \n" + ex);
cone.cnxClose();
}
}
catch (Exception ex)
{
MessageBox.Show("Error en la base de datos\n" + ex);
cone.cnxClose();
}
}
private void button1_Click(object sender, EventArgs e)
{
ConexionCapaDatos cone = new ConexionCapaDatos();
double sueldoM;
double sueldoD;
string saldo;
string dinero = "";
try {
try{
string auxi;
int diastxt = Convert.ToInt32(textBox6.Text);
if (btn1 == 1 && btn == 1 && diastxt <= 31 && diastxt > 0)
{
////
string cadenaB = "SELECT tE.ID_empleado, SUM(tCR.importe) AS total" +
" FROM tbl_empleados tE" +
" INNER JOIN tbl_empleadoconcepto tEC ON" +
" tE.ID_Empleado = tEC.ID_Empleado" +
" INNER JOIN tbl_conceptosretributivos tCR ON" +
" tCR.ID_ConceptosR = tEC.ID_ConceptosR" +
" WHERE tCR.tipo = 'ABONO' AND tE.ID_empleado=" + ID_EMP +
" group by 1;";
OdbcCommand cmd3 = new OdbcCommand(cadenaB, cone.cnxOpen());
OdbcDataReader leer3 = cmd3.ExecuteReader();
while (leer3.Read())
{
bono = leer3.GetDouble(1);
}
cone.cnxClose();
string cadenaA = "SELECT tbl_empleados.ID_empleado ,tbl_empleados.nombre,tbl_contratos.salario " +
"FROM tbl_empleados " +
"INNER JOIN tbl_contratos ON tbl_empleados.ID_contrato = tbl_contratos.ID_contrato WHERE ID_Empleado=" + ID_EMP + ";";
OdbcCommand cmd1 = new OdbcCommand(cadenaA, cone.cnxOpen());
OdbcDataReader leer = cmd1.ExecuteReader();
while (leer.Read())
{
saldo = leer.GetString(2);
sueldo = Convert.ToDouble(saldo);
switch (mescmx)
{
case (1):
sueldoM = (sueldo / 31);
sueldoD = (sueldoM * diastxt);
sueldo = sueldoD + bono;
break;
case (2):
sueldoM = (sueldo / 28);
sueldoD = (sueldoM * diastxt);
sueldo = sueldoD + bono;
break;
case (3):
sueldoM = (sueldo / 31);
sueldoD = (sueldoM * diastxt);
sueldo = sueldoD + bono;
break;
case (4):
sueldoM = (sueldo / 30);
sueldoD = (sueldoM * diastxt);
sueldo = sueldoD + bono;
break;
case (5):
sueldoM = (sueldo / 31);
sueldoD = (sueldoM * diastxt);
sueldo = sueldoD + bono;
break;
case (6):
sueldoM = (sueldo / 30);
sueldoD = (sueldoM * diastxt);
sueldo = sueldoD + bono;
break;
case (7):
sueldoM = (sueldo / 31);
sueldoD = (sueldoM * diastxt);
sueldo = sueldoD + bono;
break;
case (8):
sueldoM = (sueldo / 31);
sueldoD = (sueldoM * diastxt);
sueldo = sueldoD + bono;
break;
case (9):
sueldoM = (sueldo / 30);
sueldoD = (sueldoM * diastxt);
sueldo = sueldoD + bono;
break;
case (10):
sueldoM = (sueldo / 31);
sueldoD = (sueldoM * diastxt);
sueldo = sueldoD + bono;
break;
case (11):
sueldoM = (sueldo / 30);
sueldoD = (sueldoM * diastxt);
sueldo = sueldoD + bono;
break;
case (12):
sueldoM = (sueldo / 31);
sueldoD = (sueldoM * diastxt);
sueldo = sueldoD + bono;
break;
}
}
cone.cnxClose();
auxi = String.Format("{0:0.00}", sueldo);
string result = auxi.Replace(",", ".");
DIA = textBox6.Text;
string cadena = "UPDATE `Percepciones` SET `Dias` = " + "'" + DIA + "'" + ", `Total` = " + "'" + result + "'" + " WHERE (Percepciones.ID_Percepcion=" +
"\"" + ID_PER + "\"" + " and Percepciones.ID_Empleado=" + ID_EMP + ");";
OdbcCommand cmd = new OdbcCommand(cadena, cone.cnxOpen());
cmd.ExecuteNonQuery();
cone.cnxClose();
DataTable dt = new DataTable();
cadena = "SELECT ID_Empleado, ID_Percepcion, Total, Dias FROM Percepciones" +
" WHERE ID_Percepcion=" + "\"" + ID_PER + "\"" + ";";
OdbcDataAdapter dta = new OdbcDataAdapter(cadena, cone.cnxOpen());
DataSet dst = new DataSet();
dta.Fill(dst);
dt = dst.Tables[0];
dataGridView1.DataSource = dt;
MessageBox.Show("Actualizado");
cone.cnxClose();
}
else
{
MessageBox.Show("Debe usar primero el boton de Actualizar\n o Los dias no deben superar los 31 ni menor a 1");
}
}
catch (Exception ex)
{
MessageBox.Show("HUBO UN ERROR \n" + ex);
}
}
catch (OdbcException ex)
{
MessageBox.Show("ERROE EN LA BASE DE DATOS \n" + ex);
}
}
private void button3_Click(object sender, EventArgs e)
{
//textBox1.Text = this.dataGridView1.CurrentCell.Value.ToString();
ConexionCapaDatos cone = new ConexionCapaDatos();
DataGridViewRow row = dataGridView1.CurrentRow;
string cadenaN, cadenaA, cadenaP;
try
{
try
{
if (btn == 1)
{
textBox6.ReadOnly = false;
textBox1.Text = row.Cells[0].Value.ToString();
textBox7.Text = row.Cells[2].Value.ToString();
textBox6.Text = row.Cells[3].Value.ToString();
ID_PER = row.Cells[1].Value.ToString();
ID_EMP = textBox1.Text;
DIA = textBox6.Text;
cadenaN = "select nombre from tbl_empleados where tbl_empleados.ID_Empleado=" + ID_EMP + ";";
OdbcCommand cmd = new OdbcCommand(cadenaN, cone.cnxOpen());
OdbcDataReader leer = cmd.ExecuteReader();
while (leer.Read())
{
textBox3.Text = leer.GetString(0);
}
cone.cnxClose();
cadenaA = "select tbl_empleados.ID_Empleado, tbl_areas.Nombre from tbl_empleados" +
" inner join tbl_areas on tbl_areas.ID_Area = tbl_empleados.ID_Area" +
" WHERE tbl_empleados.ID_Empleado = " +
ID_EMP + "; ";
OdbcCommand cmd1 = new OdbcCommand(cadenaA, cone.cnxOpen());
OdbcDataReader leer1 = cmd1.ExecuteReader();
while (leer1.Read())
{
textBox4.Text = leer1.GetString(1);
}
cone.cnxClose();
cadenaP = "select tbl_empleados.ID_Empleado, tbl_puestos.Nombre from tbl_empleados" +
" inner join tbl_puestos on tbl_puestos.ID_Puesto = tbl_empleados.ID_Puesto" +
" WHERE tbl_empleados.ID_Empleado =" +
ID_EMP + "; ";
OdbcCommand cmd2 = new OdbcCommand(cadenaP, cone.cnxOpen());
OdbcDataReader leer2 = cmd2.ExecuteReader();
while (leer2.Read())
{
textBox2.Text = leer2.GetString(1);
}
cone.cnxClose();
btn = 1;
btn1 = 1;
}
else
{
MessageBox.Show("Debe usar primero el boton de Seleccionar");
}
}
catch (OdbcException ex)
{
MessageBox.Show("Error en la base de datos \n" + ex);
}
}
catch (Exception ex)
{
MessageBox.Show("Error en la base de datos\n" + ex);
}
}
private string mLlenarPolizasMicroplaneSQL()
{
string dsn = textBox1.Text;
dsn = "DSN=" + textBox1.Text;
dsn = "DSN=" + textBox1.Text + ";UID=Reports;Pwd=;";
OdbcConnection DbConnection = new OdbcConnection(dsn);
try
{
DbConnection.Open();
}
catch (Exception eeeee)
{
return("");
}
int mes = 0;
switch (comboBox1.Text)
{
case "Enero": mes = 1; break;
case "Febrero": mes = 2; break;
case "Marzo": mes = 3; break;
case "Abril": mes = 4; break;
case "Mayo": mes = 5; break;
case "Junio": mes = 6; break;
case "Julio": mes = 7; break;
case "Agosto": mes = 8; break;
case "Septiembre": mes = 9; break;
case "Octubre": mes = 10; break;
case "Noviembre": mes = 11; break;
case "Diciembre": mes = 12; break;
}
string ssql = " SELECT GBKMUT.dagbknr as Jrnl, GBKMUT.bkstnr as [Entry no.], ltrim(str(year(GBKMUT.datum)))+replace(str(month(GBKMUT.datum),2),' ','0')+replace(str(day(GBKMUT.datum),2),' ','0') as Date, " +
"GBKMUT.reknr as Account, " +
" GRTBK.oms25_0 as [Account description], " +
" GBKMUT.faktuurnr as [Our Ref], GBKMUT.docnumber as [Your Reference], GBKMUT.bkstnr_sub as [SO no.], GBKMUT.bdr_val as cantidad, GBKMUT.valcode as [Cur.], GBKMUT.oms25 as Description, GBKMUT.crdnr as [Vendor Number], " +
" VENDOR.cmp_code , " +
" VENDOR.cmp_name as Vendor, " +
" GBKMUT.res_id, GBKMUT.DocAttachmentID " +
" FROM GBKMUT " +
" join GRTBK on GBKMUT.reknr = GRTBK.reknr " +
" join CICMPY as VENDOR on ltrim(GBKMUT.crdnr) = ltrim(VENDOR.cmp_code) " +
" WHERE (month(datum)=" + mes + ") AND (year(datum)=" + textBox2.Text + ") AND (GBKMUT.dagbknr In (160,165,200,310)) " +
" ORDER BY GBKMUT.bkstnr ";
OdbcCommand DbCommand = DbConnection.CreateCommand();
DbCommand.CommandText = ssql;
OdbcDataReader dr = DbCommand.ExecuteReader();
//string aNombreArchivo = botonExcel1.mRegresarNombre();
/*
* string aNombreArchivo = textBox1.Text;
* OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + aNombreArchivo + ";Extended Properties='Excel 12.0 xml;HDR=YES;'");
*
* conn.Open();
* System.Data.OleDb.OleDbCommand cmd = new OleDbCommand();
* cmd.Connection = conn;
* cmd.CommandText = "SELECT * FROM [Sheet1$]";
* try
* {
* cmd.ExecuteNonQuery();
* }
* catch (Exception ee)
* {
* return ee.Message;
* }
*
* long xxx;
* xxx = 1000000;
* */
// System.Data.OleDb.OleDbDataReader dr;
//dr = DbCommand.ExecuteReader();
Boolean noseguir = false;
_RegPolizas.Clear();
int ifolio = 0;
int lfolio = -1;
if (dr.HasRows)
{
while (noseguir == false)
{
dr.Read();
try
{
ifolio = int.Parse(dr[1].ToString());
}
catch (Exception e)
{
_RegPolizas.Add(_Poliza);
noseguir = true;
break;
}
if (ifolio != lfolio)
{
if (lfolio != -1)
{
_RegPolizas.Add(_Poliza);
}
_Poliza = null;
_Poliza = new Poliza();
_Poliza.Folio = ifolio;
switch (dr["Jrnl"].ToString().Trim())
{
case "160":
_Poliza.TipoPol = 2;
//_Poliza.TipoPol = int(SDKCONTPAQNGLib.ETIPOPOLIZA.TIPO_INGRESOS);
break;
case "165":
_Poliza.TipoPol = 1;
break;
case "200":
_Poliza.TipoPol = 3;
break;
case "310":
_Poliza.TipoPol = 3;
break;
}
string lfecha = dr["Date"].ToString().Trim();
int primerdiagonal = lfecha.IndexOf('/', 0);
int segundadiagonal = lfecha.IndexOf('/', primerdiagonal + 1);
_Poliza.Concepto = dr["Vendor"].ToString().Trim();
string ldia = lfecha.Substring(6, 2);
string lanio = lfecha.Substring(0, 4);
string lmes = lfecha.Substring(4, 2);
/*
* string ldia = lfecha.Substring(0, primerdiagonal);
* string lanio = lfecha.Substring(segundadiagonal + 1);
* string lmes = lfecha.Substring(primerdiagonal + 1, segundadiagonal - (primerdiagonal + 1));
*/
_Poliza.FechaAlta = DateTime.Parse(ldia.ToString() + "/" + lmes.ToString() + "/" + lanio.ToString());
lfolio = _Poliza.Folio;
//_Poliza.TipoPol = 1;
}
MovPoliza lRegmovto = new MovPoliza();
lRegmovto.cuenta = dr["Account"].ToString();
decimal cantidad = decimal.Parse(dr["Cantidad"].ToString());
if (cantidad < 0)
{
lRegmovto.credito = cantidad * -1;
lRegmovto.debito = 0;
}
else
{
lRegmovto.credito = 0;
lRegmovto.debito = cantidad;
}
/*
*
* string credito = dr["Credit"].ToString();
* if (credito == "")
* lRegmovto.credito = 0;
* else
* lRegmovto.credito = decimal.Parse(credito);
*
* string debito = dr["Debit"].ToString();
* if (debito == "")
* lRegmovto.debito = 0;
* else
* lRegmovto.debito = decimal.Parse(debito);
*/
lRegmovto.concepto = dr["Your reference"].ToString();
lRegmovto.sn = "";
_Poliza._RegMovtos.Add(lRegmovto);
_Poliza.sMensaje = "";
}
}
return("");
}
void Button1Click(object sender, EventArgs e)
{
// add
OdbcCommand cmd0 = new OdbcCommand();
OdbcDataReader reader0 = null;
if (cnn.State == System.Data.ConnectionState.Closed)
{
AddLogString("Невозможно установить соединение с БД (Oracle, MySql)");
AddLogString(" выполнение алгоритма - прервано..");
cnn.Close();
return;
}
cmd0.Connection = cnn;
decimal id_cnt = 0;
int i = 0; // можем создать 100 систем сбора, берем ближайшее! свободное! целое число
for (i = 1; i <= 100; i++)
{
cmd0.CommandText = "select count(*) from sys_tbllst where upper(table_name) like 'DA_V_LST_" + i.ToString() + "'";
try { reader0 = cmd0.ExecuteReader(); }
catch (Exception ex7) { continue; }
if (reader0.HasRows)
{
while (reader0.Read())
{
id_cnt = reader0.GetDecimal(0);
}
}
reader0.Close();
if (id_cnt == 0)
{
DialogResult result = MessageBox.Show("Создать Сегмент Сбора с номером = " + i.ToString(), "?", MessageBoxButtons.OKCancel, MessageBoxIcon.Question);
if (result == System.Windows.Forms.DialogResult.Cancel)
{
break;
}
cmd0.CommandType = System.Data.CommandType.StoredProcedure;
cmd0.Parameters.Clear();
cmd0.CommandText = "{call RSDUADMIN.DA_CREATE_NEW_SEGMENT_P( ? )}";
//PROCEDURE RSDUADMIN.DA_create_new_segment_p (pTOP_ID NUMBER)
OdbcParameter param = new OdbcParameter();
param.Direction = System.Data.ParameterDirection.Input;
param.OdbcType = OdbcType.Numeric;
param.ParameterName = "pTOP_ID";
param.Value = i;
//param.Size = 1024 ;
cmd0.Parameters.Add(param);
try { cmd0.ExecuteNonQuery(); }
catch (Exception ex11) { AddLogString("Ошибка вызова процедуры =" + ex11.Message); }
AddLogString("Проверьте список Систем Сбора !");
break;
}
}
// перечитываем из базы
Button3Click(sender, e);
}
public Move(int deviceNumber, int tableNotReadyNumber)
{
DeviceNumber = deviceNumber;
TableNotReadyNumber = tableNotReadyNumber;
Device device = AppData.DeviceList[deviceNumber];
NewRoundNumber = device.RoundNumber + 1;
List <MoveOption> moveOptionsList = new List <MoveOption>();
using (OdbcConnection connection = new OdbcConnection(AppData.DBConnectionString))
{
connection.Open();
if (AppData.IsIndividual)
{
string SQLString = $"SELECT Table, NSPair, EWPair, South, West FROM RoundData WHERE Section={device.SectionID} AND Round={NewRoundNumber}";
OdbcCommand cmd = new OdbcCommand(SQLString, connection);
OdbcDataReader reader = null;
try
{
ODBCRetryHelper.ODBCRetry(() =>
{
reader = cmd.ExecuteReader();
while (reader.Read())
{
MoveOption tempMoveOption = new MoveOption()
{
TableNumber = reader.GetInt32(0),
North = reader.GetInt32(1),
East = reader.GetInt32(2),
South = reader.GetInt32(3),
West = reader.GetInt32(4),
};
moveOptionsList.Add(tempMoveOption);
}
});
}
finally
{
reader.Close();
cmd.Dispose();
}
// Try Direction = North
MoveOption moveOption = moveOptionsList.Find(x => x.North == device.PairNumber);
if (moveOption != null)
{
NewTableNumber = moveOption.TableNumber;
NewDirection = "North";
Stay = (NewTableNumber == device.TableNumber && NewDirection == device.Direction);
}
else
{
// Try Direction = South
moveOption = moveOptionsList.Find(x => x.South == device.PairNumber);
if (moveOption != null)
{
NewTableNumber = moveOption.TableNumber;
NewDirection = "South";
Stay = (NewTableNumber == device.TableNumber && NewDirection == device.Direction);
}
else
{
// Try Direction = East
moveOption = moveOptionsList.Find(x => x.East == device.PairNumber);
if (moveOption != null)
{
NewTableNumber = moveOption.TableNumber;
NewDirection = "East";
Stay = (NewTableNumber == device.TableNumber && NewDirection == device.Direction);
}
else
{
// Try Direction = West
moveOption = moveOptionsList.Find(x => x.West == device.PairNumber);
if (moveOption != null)
{
NewTableNumber = moveOption.TableNumber;
NewDirection = "West";
Stay = (NewTableNumber == device.TableNumber && NewDirection == device.Direction);
}
else // No move info found - move to sit out
{
NewTableNumber = 0;
NewDirection = "";
Stay = false;
}
}
}
}
}
else // Not individual, so find pair
{
string SQLString = $"SELECT Table, NSPair, EWPair FROM RoundData WHERE Section={device.SectionID} AND Round={NewRoundNumber}";
OdbcCommand cmd = new OdbcCommand(SQLString, connection);
OdbcDataReader reader = null;
try
{
ODBCRetryHelper.ODBCRetry(() =>
{
reader = cmd.ExecuteReader();
while (reader.Read())
{
MoveOption tempMoveOption = new MoveOption()
{
TableNumber = reader.GetInt32(0),
North = reader.GetInt32(1),
East = reader.GetInt32(2),
};
moveOptionsList.Add(tempMoveOption);
}
});
}
finally
{
reader.Close();
cmd.Dispose();
}
MoveOption moveOption = null;
if (device.Direction == "North" || device.Direction == "South")
{
moveOption = moveOptionsList.Find(x => x.North == device.PairNumber);
}
else
{
moveOption = moveOptionsList.Find(x => x.East == device.PairNumber);
}
if (moveOption != null)
{
NewTableNumber = moveOption.TableNumber;
NewDirection = device.Direction;
}
else
{
// Pair changes Direction
if (device.Direction == "North" || device.Direction == "South")
{
moveOption = moveOptionsList.Find(x => x.East == device.PairNumber);
}
else
{
moveOption = moveOptionsList.Find(x => x.North == device.PairNumber);
}
if (moveOption != null)
{
NewTableNumber = moveOption.TableNumber;
if (device.Direction == "North")
{
NewDirection = "East";
}
else if (device.Direction == "East")
{
NewDirection = "North";
}
else if (device.Direction == "South")
{
NewDirection = "West";
}
else if (device.Direction == "West")
{
NewDirection = "South";
}
}
else // No move info found - move to sit out
{
NewTableNumber = 0;
NewDirection = device.Direction;
}
}
Stay = (NewTableNumber == device.TableNumber && NewDirection == device.Direction);
}
}
}
public void RefreshDB( )
{
OdbcCommand cmd0 = new OdbcCommand();
OdbcCommand cmd1 = new OdbcCommand();
OdbcDataReader reader = null;
OdbcDataReader reader1 = null;
if (cnn.State == System.Data.ConnectionState.Closed)
{
AddLogString("Невозможно установить соединение с БД (Oracle, MySql)");
AddLogString(" выполнение алгоритма - прервано..");
cnn.Close();
return;
}
cmd0.Connection = cnn;
cmd1.Connection = cnn;
cmd0.CommandText = "select sys_tbllst.*,FROM_DT1970(LAST_UPDATE) from sys_tbllst where upper(table_name) like 'DA_V_LST_%'";
try
{
reader = cmd0.ExecuteReader();
}
catch (Exception ex7)
{
AddLogString(" выполнение алгоритма - прервано.." + cmd0.CommandText);
return;
}
string[] arr = new string[7];
ListViewItem itm;
decimal id_cnt;
if (reader.HasRows)
{
while (reader.Read())
{
//ID NUMBER(11),
//ID_NODE NUMBER(11),
//NAME VARCHAR2(255 BYTE) CONSTRAINT SYS_TBLLST_NAME_NN NOT NULL,
//TABLE_NAME VARCHAR2(63 BYTE) CONSTRAINT SYS_TBLLST_TABLE_NAME_NN NOT NULL,
//ID_TYPE NUMBER(11) CONSTRAINT SYS_TBLLST_ID_TYPE_NN NOT NULL,
//DEFINE_ALIAS VARCHAR2(63 BYTE) CONSTRAINT SYS_TBLLST_DEFINE_ALIAS_NN NOT NULL,
//LAST_UPDATE NUMBER(11)
arr[0] = reader.GetDecimal(0).ToString();
arr[1] = reader.GetDecimal(1).ToString();
arr[2] = reader.GetString(2);
arr[3] = reader.GetString(3);
arr[4] = reader.GetDecimal(4).ToString();
arr[5] = reader.GetString(5);
//arr[6] = reader.GetDecimal(6).ToString();
arr[6] = reader.GetString(7);
itm = new ListViewItem(arr);
id_cnt = 0;
cmd1.CommandText = "select count(id) from sys_tree21 where id_lsttbl in (select id from sys_tbllst where upper(table_name) like '" + arr[3] + "');";
try
{
reader1 = cmd1.ExecuteReader();
}
catch (Exception ex8)
{
AddLogString(" ошибка : " + cmd1.CommandText);
}
if (reader1.HasRows)
{
while (reader1.Read())
{
id_cnt = reader1.GetDecimal(0);
}
}
reader1.Close();
if (id_cnt > 0)
{
itm.Checked = false;
}
else
{
itm.Checked = true;
}
if (InvokeRequired)
{
listView1.Invoke((Action) delegate { listView1.Items.Add(itm); });
}
else
{
listView1.Items.Add(itm);
}
Application.DoEvents();
}
}
reader.Close();
return;
}
protected void btnYes_Click(object sender, EventArgs e)
{
if (ViewState["action"].ToString() == "Save")
{
#region save
con = obje.NewConnection();
DateTime date = DateTime.Now;
string dat = date.ToString("yyyy-MM-dd") + " " + date.ToString("HH:mm:ss");
if (btnSave.Text == "Save")
{
OdbcTransaction odbTrans = null;
try
{
odbTrans = con.BeginTransaction();
OdbcCommand f11 = new OdbcCommand("CALL selectcond(?,?,?)", con);
f11.CommandType = CommandType.StoredProcedure;
f11.Parameters.AddWithValue("tblname", "m_userprevsetting");
f11.Parameters.AddWithValue("attribute", "prev_level");
f11.Parameters.AddWithValue("conditionv", "prev_level =" + int.Parse(txtUserlevel.Text) + " and rowstatus<>2");
OdbcDataAdapter dacnt3 = new OdbcDataAdapter(f11);
DataTable dtt3 = new DataTable();
f11.Transaction = odbTrans;
dacnt3.Fill(dtt3);
if (dtt3.Rows.Count > 0)
{
lblOk.Text = "Level already exists"; lblHead.Text = "Tsunami ARMS - Warning";
pnlOk.Visible = true;
pnlYesNo.Visible = false;
ModalPopupExtender2.Show();
return;
}
//user prev setting
if (cmbExecute.SelectedItem.ToString() == "Yes")
{
b = 1;
}
else if (cmbExecute.SelectedItem.ToString() == "No")
{
b = 0;
}
OdbcCommand f12 = new OdbcCommand("CALL selectcond(?,?,?)", con);
f12.CommandType = CommandType.StoredProcedure;
f12.Parameters.AddWithValue("tblname", "m_sub_form");
f12.Parameters.AddWithValue("attribute", "form_id");
f12.Parameters.AddWithValue("conditionv", "displayname='" + cmbDefault.SelectedItem.Text.ToString() + "' and status<>'2'");
f12.Transaction = odbTrans;
OdbcDataAdapter dacnt31 = new OdbcDataAdapter(f12);
DataTable dtt31 = new DataTable();
dacnt31.Fill(dtt31);
fid = Convert.ToInt32(dtt31.Rows[0][0].ToString());
id = Convert.ToInt32(Session["userid"].ToString());
OdbcCommand cmd5 = new OdbcCommand("CALL savedata(?,?)", con);
cmd5.CommandType = CommandType.StoredProcedure;
cmd5.Parameters.AddWithValue("tblname", "m_userprevsetting");
string aaa = "" + int.Parse(txtUserlevel.Text) + "," + fid + ",'" + b + "'," + id + "," + id + ",'" + dat + "'," + id + ",'" + dat + "'," + "0" + "";
cmd5.Parameters.AddWithValue("val", "" + int.Parse(txtUserlevel.Text) + "," + fid + ",'" + b + "'," + id + "," + id + ",'" + dat + "'," + id + ",'" + dat + "'," + "0" + "");
cmd5.Transaction = odbTrans;
cmd5.ExecuteNonQuery();
OdbcCommand Del = new OdbcCommand("DELETE from m_userprev_formset where prev_level=" + int.Parse(txtUserlevel.Text) + "", con);
Del.Transaction = odbTrans;
Del.ExecuteNonQuery();
OdbcCommand cmd3 = new OdbcCommand("select max(prev_forms_id) from m_userprev_formset", con);
cmd3.Transaction = odbTrans;
if (Convert.IsDBNull(cmd3.ExecuteScalar()) == true)
{
o = 1;
}
else
{
o = Convert.ToInt32(cmd3.ExecuteScalar());
o = o + 1;
}
for (k = 0; k < lstSelectedform.Items.Count; k++)
{
OdbcCommand cmd2 = new OdbcCommand("select max(prev_forms_id) from m_userprev_formset", con);
cmd2.Transaction = odbTrans;
if (Convert.IsDBNull(cmd2.ExecuteScalar()) == true)
{
n = 1;
}
else
{
n = Convert.ToInt32(cmd2.ExecuteScalar());
n = n + 1;
}
OdbcCommand f13 = new OdbcCommand("CALL selectcond(?,?,?)", con);
f13.CommandType = CommandType.StoredProcedure;
f13.Parameters.AddWithValue("tblname", "m_sub_form");
f13.Parameters.AddWithValue("attribute", "form_id");
f13.Parameters.AddWithValue("conditionv", "displayname='" + lstSelectedform.Items[k].Text.ToString() + "' and status<>'2'");
OdbcDataAdapter dacnt32 = new OdbcDataAdapter(f13);
f13.Transaction = odbTrans;
DataTable dtt32 = new DataTable();
dacnt32.Fill(dtt32);
fid1 = Convert.ToInt32(dtt32.Rows[0][0].ToString());
OdbcCommand cmd6 = new OdbcCommand("CALL savedata(?,?)", con);
cmd6.CommandType = CommandType.StoredProcedure;
cmd6.Parameters.AddWithValue("tblname", "m_userprev_formset");
cmd6.Parameters.AddWithValue("val", "" + n + "," + int.Parse(txtUserlevel.Text) + "," + fid1 + "," + id + ",'" + dat + "'," + id + ",'" + dat + "'," + "0" + "");
cmd6.Transaction = odbTrans;
cmd6.ExecuteNonQuery();
}
odbTrans.Commit();
cmbDefault.SelectedIndex = -1;
cmbExecute.SelectedIndex = -1;
con.Close();
clear();
dguserlevel();
lblOk.Text = " Data saved successfully "; lblHead.Text = "Tsunami ARMS - Confirmation";
pnlOk.Visible = true;
pnlYesNo.Visible = false;
ModalPopupExtender2.Show();
}
catch
{
odbTrans.Rollback();
ViewState["action"] = "NILL";
okmessage("Tsunami ARMS - Warning", "Error in saving ");
}
}
#endregion
ViewState["option"] = "NIL";
ViewState["action"] = "NIL";
}
else if (ViewState["action"].ToString() == "Edit")
{
#region edit
DateTime date = DateTime.Now;
string dat = date.ToString("yyyy-MM-dd") + " " + date.ToString("HH:mm:ss");
listboxselection.Enabled = false;
vlistbox.Enabled = false;
btnSave.CausesValidation = false;
con = obje.NewConnection();
OdbcTransaction odbTrans = null;
try
{
q = int.Parse(dtgUsergrid.SelectedRow.Cells[1].Text);
odbTrans = con.BeginTransaction();
#region log table
OdbcCommand cmd1 = new OdbcCommand("select max(rowno) from m_userprevsetting_log", con);
cmd1.Transaction = odbTrans;
if (Convert.IsDBNull(cmd1.ExecuteScalar()) == true)
{
rn = 1;
}
else
{
rn = Convert.ToInt32(cmd1.ExecuteScalar());
rn = rn + 1;
}
OdbcCommand cmd46p = new OdbcCommand("CALL selectcond(?,?,?)", con);
cmd46p.CommandType = CommandType.StoredProcedure;
cmd46p.Parameters.AddWithValue("tblname", "m_userprevsetting");
cmd46p.Parameters.AddWithValue("attribute", "*");
cmd46p.Parameters.AddWithValue("conditionv", "prev_level=" + q + "");
cmd46p.Transaction = odbTrans;
OdbcDataAdapter dacnt46p = new OdbcDataAdapter(cmd46p);
DataTable dtt46p = new DataTable();
dacnt46p.Fill(dtt46p);
OdbcCommand cmd55 = new OdbcCommand("CALL savedata(?,?)", con);
cmd55.CommandType = CommandType.StoredProcedure;
cmd55.Parameters.AddWithValue("tblname", "m_userprevsetting_log");
DateTime Date1 = DateTime.Parse(dtt46p.Rows[0]["createdon"].ToString());
string Date2 = Date1.ToString("yyyy-MM-dd HH:mm:ss");
string aaa = "" + Convert.ToInt32(dtt46p.Rows[0]["prev_level"]) + "," + Convert.ToInt32(dtt46p.Rows[0]["defaultform_id"]) + ",'" + dtt46p.Rows[0]["execoverride"].ToString() + "'," + Convert.ToInt32(dtt46p.Rows[0]["userid"]) + "," + Convert.ToInt32(dtt46p.Rows[0]["createdby"]) + ",'" + Date2.ToString() + "'," + Convert.ToInt32(dtt46p.Rows[0]["rowstatus"]) + "," + rn + "";
cmd55.Parameters.AddWithValue("val", "" + Convert.ToInt32(dtt46p.Rows[0]["prev_level"]) + "," + Convert.ToInt32(dtt46p.Rows[0]["defaultform_id"]) + ",'" + dtt46p.Rows[0]["execoverride"].ToString() + "'," + Convert.ToInt32(dtt46p.Rows[0]["userid"]) + "," + Convert.ToInt32(dtt46p.Rows[0]["createdby"]) + ",'" + Date2.ToString() + "'," + Convert.ToInt32(dtt46p.Rows[0]["rowstatus"]) + "," + rn + "");
cmd55.Transaction = odbTrans;
cmd55.ExecuteNonQuery();
OdbcCommand crr1 = new OdbcCommand("delete from m_userprev_formset_log where prev_level=" + q + "", con);
crr1.Transaction = odbTrans;
crr1.ExecuteNonQuery();
OdbcCommand cmd6 = new OdbcCommand("select max(rowno)from m_userprev_formset_log", con);
cmd6.Transaction = odbTrans;
if (Convert.IsDBNull(cmd6.ExecuteScalar()) == true)
{
n = 1;
}
else
{
n = Convert.ToInt32(cmd6.ExecuteScalar());
n = n + 1;
}
OdbcCommand f16 = new OdbcCommand("CALL selectcond(?,?,?)", con);
f16.CommandType = CommandType.StoredProcedure;
f16.Parameters.AddWithValue("tblname", "m_userprev_formset");
f16.Parameters.AddWithValue("attribute", "*");
f16.Parameters.AddWithValue("conditionv", "prev_level=" + q + "");
f16.Transaction = odbTrans;
OdbcDataAdapter dacnt3o = new OdbcDataAdapter(f16);
DataTable dtt3o = new DataTable();
dacnt3o.Fill(dtt3o);
OdbcCommand cmd24 = new OdbcCommand("CALL savedata(?,?)", con);
cmd24.CommandType = CommandType.StoredProcedure;
cmd24.Parameters.AddWithValue("tblname", "m_userprev_formset_log");
DateTime Date4 = DateTime.Parse(dtt3o.Rows[0]["createdon"].ToString());
string Date5 = Date4.ToString("yyyy-MM-dd HH:mm:ss");
cmd24.Parameters.AddWithValue("val", "" + Convert.ToInt32(dtt3o.Rows[0]["prev_forms_id"]) + "," + Convert.ToInt32(dtt3o.Rows[0]["prev_level"]) + "," + Convert.ToInt32(dtt3o.Rows[0]["form_id"]) + "," + Convert.ToInt32(dtt3o.Rows[0]["createdby"]) + ",'" + Date5.ToString() + "'," + "1" + "," + n + "");
cmd24.Transaction = odbTrans;
cmd24.ExecuteNonQuery();
#endregion
OdbcCommand defa1 = new OdbcCommand("select form_id from m_sub_form where displayname='" + cmbDefault.SelectedItem.Text.ToString() + "' and status<>'2'", con);
defa1.Transaction = odbTrans;
OdbcDataReader defr1 = defa1.ExecuteReader();
if (defr1.Read())
{
fid = Convert.ToInt32(defr1["form_id"].ToString());
}
id = Convert.ToInt32(Session["userid"].ToString());
m1 = int.Parse(dtgUsergrid.SelectedRow.Cells[1].Text);
OdbcCommand cmd25 = new OdbcCommand("call updatedata(?,?,?)", con);
if (cmbExecute.SelectedItem.ToString() == "Yes")
{
b = 1;
}
else if (cmbExecute.SelectedItem.ToString() == "No")
{
b = 0;
}
cmd25.CommandType = CommandType.StoredProcedure;
cmd25.Parameters.AddWithValue("tablename", "m_userprevsetting");
cmd25.Parameters.AddWithValue("valu", "prev_level=" + int.Parse(txtUserlevel.Text) + ",defaultform_id=" + fid + ",execoverride=" + b + ",userid=" + id + ",updateddate='" + dat + "',rowstatus=" + "1" + "");
cmd25.Parameters.AddWithValue("convariable", "prev_level=" + m1 + "");
cmd25.Transaction = odbTrans;
cmd25.ExecuteNonQuery();
OdbcCommand crr = new OdbcCommand("delete from m_userprev_formset where prev_level=" + m1 + "", con);
crr.Transaction = odbTrans;
crr.ExecuteNonQuery();
for (k = 0; k < lstSelectedform.Items.Count; k++)
{
OdbcCommand cmd6a = new OdbcCommand("select max(prev_forms_id)from m_userprev_formset", con);
cmd6a.Transaction = odbTrans;
if (Convert.IsDBNull(cmd6a.ExecuteScalar()) == true)
{
n = 1;
}
else
{
n = Convert.ToInt32(cmd6a.ExecuteScalar());
n = n + 1;
}
OdbcCommand f15 = new OdbcCommand("CALL selectcond(?,?,?)", con);
f15.CommandType = CommandType.StoredProcedure;
f15.Parameters.AddWithValue("tblname", "m_sub_form");
f15.Parameters.AddWithValue("attribute", "form_id");
f15.Parameters.AddWithValue("conditionv", "displayname='" + lstSelectedform.Items[k].Text.ToString() + "' and status<>'2'");
OdbcDataAdapter dacnt3a = new OdbcDataAdapter(f15);
f15.Transaction = odbTrans;
DataTable dtt3a = new DataTable();
dacnt3a.Fill(dtt3a);
fid1 = Convert.ToInt32(dtt3a.Rows[0]["form_id"].ToString());
OdbcCommand cmd61 = new OdbcCommand("CALL savedata(?,?)", con);
cmd61.CommandType = CommandType.StoredProcedure;
cmd61.Parameters.AddWithValue("tblname", "m_userprev_formset");
cmd61.Parameters.AddWithValue("val", "" + n + "," + int.Parse(txtUserlevel.Text) + "," + fid1 + "," + id + ",'" + dat + "'," + id + ",'" + dat + "'," + "1" + "");
cmd61.Transaction = odbTrans;
cmd61.ExecuteNonQuery();
}
odbTrans.Commit();
btnSave.Text = "Save";
cmbExecute.SelectedIndex = -1;
cmbDefault.SelectedIndex = -1;
con.Close();
clear();
dguserlevel();
lblOk.Text = " Data updated successfully "; lblHead.Text = "Tsunami ARMS - Confirmation";
pnlOk.Visible = true;
pnlYesNo.Visible = false;
ModalPopupExtender2.Show();
}
catch
{
odbTrans.Rollback();
ViewState["action"] = "NILL";
okmessage("Tsunami ARMS - Warning", "Error in saving ");
}
#endregion
ViewState["option"] = "NIL";
ViewState["action"] = "NIL";
}
#region CHECK PRIVILEGE LEVEL IS ALREADY EXISTS OR NOT
else if (ViewState["action"].ToString() == "Level")
{
this.ScriptManager1.SetFocus(txtUserlevel);
txtUserlevel.Text = Session["prevlevel"].ToString();
lstSelectform.SelectedIndex = -1;
con = obje.NewConnection();
OdbcCommand PLevel = new OdbcCommand("DELETE from m_userprevsetting WHERE prev_level=" + int.Parse(txtUserlevel.Text) + "", con);
PLevel.ExecuteNonQuery();
con.Close();
ViewState["option"] = "NIL";
ViewState["action"] = "NIL";
}
#endregion
else if (ViewState["action"].ToString() == "Delete")
{
#region delete
DateTime date = DateTime.Now;
string dat = date.ToString("yyyy-MM-dd") + " " + date.ToString("HH:mm:ss");
con = obje.NewConnection();
OdbcTransaction odbTrans = null;
try
{
odbTrans = con.BeginTransaction();
q = int.Parse(dtgUsergrid.SelectedRow.Cells[1].Text);
id = Convert.ToInt32(Session["userid"].ToString());
OdbcCommand cmd28 = new OdbcCommand("call updatedata(?,?,?)", con);
cmd28.CommandType = CommandType.StoredProcedure;
cmd28.Parameters.AddWithValue("tablename", "m_userprevsetting");
cmd28.Parameters.AddWithValue("valu", "rowstatus=" + "2" + ",userid=" + id + "");
cmd28.Parameters.AddWithValue("convariable", "prev_level=" + q + "");
cmd28.Transaction = odbTrans;
cmd28.ExecuteNonQuery();
OdbcCommand cmd29 = new OdbcCommand("CALL updatedata(?,?,?)", con);
cmd29.CommandType = CommandType.StoredProcedure;
cmd29.Parameters.AddWithValue("tablename", "m_userprev_formset");
cmd29.Parameters.AddWithValue("valu", "rowstatus=" + "2" + "");
cmd29.Parameters.AddWithValue("convariable", "prev_level=" + q + "");
cmd29.Transaction = odbTrans;
cmd29.ExecuteNonQuery();
odbTrans.Commit();
lblOk.Text = " Data successfully deleted "; lblHead.Text = "Tsunami ARMS - Confirmation";
pnlOk.Visible = true;
pnlYesNo.Visible = false;
ModalPopupExtender2.Show();
con.Close();
clear();
dguserlevel();
}
catch
{
odbTrans.Rollback();
ViewState["action"] = "NILL";
okmessage("Tsunami ARMS - Warning", "Error in Deleting ");
}
#endregion
}
}
private void ShowStatistics()
{
lvStatistics.BeginUpdate();
lvStatistics.Items.Clear();
//string SQL = "SELECT TagID, FirstName, LastName, Location, Type, Contaminated, AvgTimeContaminated, Washed FROM (" +
// "SELECT sani.TagID, COUNT(*) as Contaminated, AVG(TIME_TO_SEC(TIMEDIFF(sani1.Time, sani.Time))) AS AvgTimeContaminated, sani.Location, sani.Type " +
// "FROM sani, sani as sani1 " +
// "WHERE sani.TagID = sani1.TagID AND sani.index = sani1.index - 1 AND sani.SaniStatus != sani1.SaniStatus AND sani.SaniStatus = 'Wash' AND (sani1.SaniStatus = 'Exiting' OR sani1.SaniStatus = 'Fresh' OR sani1.SaniStatus = 'Normal') " +
// "GROUP BY TagID" +
// ") as t, (" +
// "SELECT COUNT(*) as Washed " +
// "FROM sani, sani as sani1 " +
// "WHERE sani.TagID = sani1.TagID AND sani.index = sani1.index - 1 AND sani.SaniStatus != sani1.SaniStatus AND (sani.SaniStatus = 'Exiting' OR sani.SaniStatus = 'Fresh' OR sani.SaniStatus = 'Normal') AND (sani1.SaniStatus = 'Exiting' OR sani.SaniStatus = 'Normal' OR sani.SaniStatus = 'Fresh') " +
// "GROUP BY sani.TagID) as u " +
// "JOIN employees WHERE TagID = ID";
string SQL = "SELECT * FROM (" +
"SELECT 'Total' as TagID, '' as FirstName, '' as LastName, '' as Location, '' as Type, Washed, Engaged, Contaminated, Violation FROM (" +
"SELECT COUNT(*) as Washed FROM sani WHERE SaniStatus = 'Alcohol Clean' OR SaniStatus = 'Soap Clean'" +
") as w, (" +
"SELECT COUNT(*) as Engaged FROM sani WHERE SaniStatus = 'Engaging Patient'" +
") as p, (" +
"SELECT COUNT(*) as Contaminated FROM sani WHERE SaniStatus = 'Contaminated Other' OR SaniStatus = 'Contaminated Patient' OR SaniStatus = 'Contaminated Bathroom'" +
") as c, (" +
"SELECT COUNT(*) as Violation FROM sani WHERE SaniStatus = 'Violation'" +
") as v " +
"UNION " +
"SELECT w.TagID, FirstName, LastName, Location, Type, Washed, Engaged, Contaminated, Violation FROM (" +
"SELECT TagID, Location, Type, COUNT(*) as Washed FROM sani WHERE SaniStatus = 'Alcohol Clean' OR SaniStatus = 'Soap Clean' GROUP BY TagID" +
") as w, (" +
"SELECT TagID, COUNT(*) as Engaged FROM sani WHERE SaniStatus = 'Engaging Patient' GROUP BY TagID" +
") as p, (" +
"SELECT TagID, COUNT(*) as Contaminated FROM sani WHERE SaniStatus = 'Contaminated Other' OR SaniStatus = 'Contaminated Patient' OR SaniStatus = 'Contaminated Bathroom' GROUP BY TagID" +
") as c, (" +
"SELECT TagID, COUNT(*) as Violation FROM sani WHERE SaniStatus = 'Violation' GROUP BY TagID" +
") as v " +
"JOIN employees WHERE w.TagID = ID) as ix " +
"ORDER BY TagID='Total' DESC, TagID ASC";
try
{
lock (MainForm.m_connection)
{
using (OdbcCommand cmd = new OdbcCommand(SQL, MainForm.m_connection))
using (OdbcDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
ListViewItem listItem = new ListViewItem(reader.GetString(0)); // TagID
listItem.SubItems.Add(reader.GetString(1)); // FirstName
listItem.SubItems.Add(reader.GetString(2)); // LastName
listItem.SubItems.Add(reader.GetString(3)); // Location
try
{
string type = reader.GetString(4); // Type
if (type.CompareTo("1") == 0)
{
listItem.SubItems.Add("Employee");
}
else if (type.CompareTo("3") == 0)
{
listItem.SubItems.Add("Visitor");
}
else
{
listItem.SubItems.Add("");
}
}
catch
{
listItem.SubItems.Add("");
}
int washed = reader.GetInt32(5);
int engaged = reader.GetInt32(6);
int contaminated = reader.GetInt32(7);
int violation = reader.GetInt32(8);
listItem.SubItems.Add(washed.ToString()); //Washed
listItem.SubItems.Add(engaged.ToString()); //Engaged
listItem.SubItems.Add(contaminated.ToString()); //Contaminated
listItem.SubItems.Add(violation.ToString()); //Violation
lvStatistics.Items.Add(listItem);
}
}
}
}
finally
{
lvStatistics.EndUpdate();
}
}
/// <summary>
///
/// insert a row in table t_RBSR_AUFW_u_Entitlement.
/// </summary>
/// <param name="StandardActivity"></param>
/// <param name="RoleType"></param>
/// <param name="System"></param>
/// <param name="Platform"></param>
/// <param name="EntitlementName"></param>
/// <param name="EntitlementValue"></param>
/// <param name="Application"></param>
/// <param name="CHECKSUM"></param>
/// <returns>The integer ID of the new object.</returns>
public int NewEntitlement(string StandardActivity, string RoleType, string System, string Platform, string EntitlementName, string EntitlementValue, string Application, string CHECKSUM)
{
int rv = 0;
DBConnect();
OdbcCommand cmd = _dbConnection.CreateCommand();
cmd.CommandText = "INSERT INTO \"t_RBSR_AUFW_u_Entitlement\"(\"c_u_StandardActivity\",\"c_u_RoleType\",\"c_u_System\",\"c_u_Platform\",\"c_u_EntitlementName\",\"c_u_EntitlementValue\",\"c_u_Application\",\"c_u_CHECKSUM\") VALUES(?,?,?,?,?,?,?,?)";
if (_dbConnection.Driver.ToLower().StartsWith("sqlsrv"))
{
cmd.CommandText += " SELECT convert(int,SCOPE_IDENTITY())";
}
if (StandardActivity == null)
{
throw new Exception("StandardActivity must not be null!");
}
cmd.Parameters.Add("c_u_StandardActivity", OdbcType.NVarChar, 50);
cmd.Parameters["c_u_StandardActivity"].Value = (StandardActivity != null ? (object)StandardActivity : DBNull.Value);
if (RoleType == null)
{
throw new Exception("RoleType must not be null!");
}
cmd.Parameters.Add("c_u_RoleType", OdbcType.NVarChar, 50);
cmd.Parameters["c_u_RoleType"].Value = (RoleType != null ? (object)RoleType : DBNull.Value);
if (System == null)
{
throw new Exception("System must not be null!");
}
cmd.Parameters.Add("c_u_System", OdbcType.NVarChar, 50);
cmd.Parameters["c_u_System"].Value = (System != null ? (object)System : DBNull.Value);
if (Platform == null)
{
throw new Exception("Platform must not be null!");
}
cmd.Parameters.Add("c_u_Platform", OdbcType.NVarChar, 50);
cmd.Parameters["c_u_Platform"].Value = (Platform != null ? (object)Platform : DBNull.Value);
if (EntitlementName == null)
{
throw new Exception("EntitlementName must not be null!");
}
cmd.Parameters.Add("c_u_EntitlementName", OdbcType.NVarChar, 100);
cmd.Parameters["c_u_EntitlementName"].Value = (EntitlementName != null ? (object)EntitlementName : DBNull.Value);
if (EntitlementValue == null)
{
throw new Exception("EntitlementValue must not be null!");
}
cmd.Parameters.Add("c_u_EntitlementValue", OdbcType.NVarChar, 1024);
cmd.Parameters["c_u_EntitlementValue"].Value = (EntitlementValue != null ? (object)EntitlementValue : DBNull.Value);
if (Application == null)
{
throw new Exception("Application must not be null!");
}
cmd.Parameters.Add("c_u_Application", OdbcType.NVarChar, 50);
cmd.Parameters["c_u_Application"].Value = (Application != null ? (object)Application : DBNull.Value);
if (CHECKSUM == null)
{
throw new Exception("CHECKSUM must not be null!");
}
cmd.Parameters.Add("c_u_CHECKSUM", OdbcType.NVarChar, 100);
cmd.Parameters["c_u_CHECKSUM"].Value = (CHECKSUM != null ? (object)CHECKSUM : DBNull.Value);
OdbcDataReader dri = cmd.ExecuteReader();
if (_dbConnection.Driver.ToLower().StartsWith("myodbc"))
{
cmd = _dbConnection.CreateCommand();
cmd.CommandText = "SELECT LAST_INSERT_ID()";
dri = cmd.ExecuteReader();
}
dri.Read();
rv = (dri.IsDBNull(0) ? 0 : (typeof(long).Equals(dri.GetFieldType(0)) ? (int)dri.GetInt64(0) : (int)dri.GetInt32(0)));
dri.Close();
if (rv == 0)
{
throw new Exception("Insert operation failed!");
}
dri.Dispose();
cmd.Dispose();
DBClose();
return(rv);
}
/// <summary>
/// Implementation of <see cref="IWorkflowScript.OnWorkflowScriptExecute" />.
/// <seealso cref="IWorkflowScript" />
/// </summary>
/// <param name="app"></param>
/// <param name="args"></param>
public void OnWorkflowScriptExecute(Hyland.Unity.Application app, Hyland.Unity.WorkflowEventArgs args)
{
try
{
// Initialize global settings
IntializeScript(ref app, ref args);
//get and clean LicenseType and Case # keywords for passing to LicEase database
KeywordType kwtLicNum = _currentDocument.DocumentType.KeywordRecordTypes.FindKeywordType(gSaveToLicNum);
string strLicNum = "";
if (kwtLicNum != null)
{
KeywordRecord keyRecLicNum = _currentDocument.KeywordRecords.Find(kwtLicNum);
if (keyRecLicNum != null)
{
Keyword kwdLicNum = keyRecLicNum.Keywords.Find(kwtLicNum);
if (kwdLicNum != null)
{
strLicNum = CleanSeedKW(kwdLicNum.ToString());
}
}
}
KeywordType kwtLicenseType = _currentDocument.DocumentType.KeywordRecordTypes.FindKeywordType(gSaveToLicType);
string strLicenseType = "";
if (kwtLicenseType != null)
{
KeywordRecord keyRecLicenseType = _currentDocument.KeywordRecords.Find(kwtLicenseType);
if (keyRecLicenseType != null)
{
Keyword kwdLicenseType = keyRecLicenseType.Keywords.Find(kwtLicenseType);
if (kwdLicenseType != null)
{
strLicenseType = CleanSeedKW(kwdLicenseType.ToString());
}
}
}
KeywordType kwtInspectionDate = _currentDocument.DocumentType.KeywordRecordTypes.FindKeywordType(gSaveToInspectVisitDate);
string strInspectionDate = "";
if (kwtInspectionDate != null)
{
KeywordRecord keyRecInspectionDate = _currentDocument.KeywordRecords.Find(kwtInspectionDate);
if (keyRecInspectionDate != null)
{
Keyword kwdInspectionDate = keyRecInspectionDate.Keywords.Find(kwtInspectionDate);
if (kwdInspectionDate != null)
{
strInspectionDate = CleanSeedKW(kwdInspectionDate.ToString());
}
}
}
if (strInspectionDate == "")
{
throw new Exception(string.Format("Search keyword {0} is blank.", gSaveToInspectVisitDate));
}
if (strLicNum == "")
{
throw new Exception(string.Format("Search keyword {0} is blank.", gSaveToLicNum));
}
if (strLicenseType == "")
{
throw new Exception(string.Format("Search keyword {0} is blank.", gSaveToLicType));
}
//access Config Item for LicEase User
string gUSER = "";
if (app.Configuration.TryGetValue("LicEaseUser", out gUSER))
{
}
//access Config Item for LicEase Password
string gPASS = "";
if (app.Configuration.TryGetValue("LicEasePassword", out gPASS))
{
}
/* COMMENT THIS SECTION OUT WHEN MOVING TO PROD */
//access Config Item for LicEase UAT ODBC
string gODBC = "";
if (app.Configuration.TryGetValue("LicEaseUAT", out gODBC))
{
}
/* UNCOMMENT THIS SECTION WHEN MOVING TO PROD
* //access Config Item for LicEase PROD ODBC
* string gODBC = "";
* if (app.Configuration.TryGetValue("LicEasePROD", out gODBC))
* {
* }
*/
string connectionString = string.Format("DSN={0};Uid={1};Pwd={2};", gODBC, gUSER, gPASS);
app.Diagnostics.WriteIf(Diagnostics.DiagnosticsLevel.Verbose, string.Format("Connection string: {0}", connectionString));
StringBuilder strSql = new StringBuilder();
strSql.Append(@"SELECT a.insp_nbr, a.key_name, a.insp_vst_id AS insp_vst_id,a.lic_type AS lic_type, a.indorg_num, a.file_num, ");
strSql.Append(@" a.lic_num AS lic_num, a.visit_num, a.visit_date,a.insp_typ, a.disposition_kw, a.city_kw, a.county_kw, a.region_kw,a.inspector_name ");
strSql.Append(@" FROM (SELECT DISTINCT TO_CHAR (insp_hist.insp_nbr) AS insp_nbr,TO_CHAR(insp_vst.insp_vst_id) AS insp_vst_id,lic.clnt_cde AS lic_type,lic.lic_nbr AS lic_num, ");
strSql.Append(@" pri_name.key_nme AS key_name,TO_CHAR (lic.xent_id) AS indorg_num,TO_CHAR (lic.file_nbr) AS file_num,TO_CHAR (insp_vst.insp_vst_nbr) AS visit_num,TO_CHAR (insp_vst.insp_vst_strt_dte) AS visit_date, ");
strSql.Append(@" insp_typ_defn.insp_typ_desc AS insp_typ,addr.addr_cty AS city_kw,cnty.cnty_desc AS county_kw,insp_regn.insp_regn_cde AS region_kw, ");
strSql.Append(@" insp_disp_typ.insp_disp_typ_desc AS disposition_kw,stff.frst_nme || '.' || stff.surnme AS inspector_name ");
strSql.Append(@" FROM insp_vst,insp_hist,insp_typ_defn,insp_disp_typ,inspr,stff,lic,clnt,NAME pri_name,insp_regn,inspr_insp_regn,LINK,addr,cnty ");
strSql.Append(@" WHERE lic.lic_nbr = '");
strSql.Append(strLicNum);
strSql.Append(@"' AND lic.clnt_cde = '");
strSql.Append(strLicenseType);
strSql.Append(@"' AND insp_hist.lic_id = lic.lic_id and insp_hist.link_id = link.link_id AND LINK.nme_id = pri_name.nme_id AND addr.addr_id = LINK.addr_id ");
strSql.Append(@" AND LINK.insp_regn_id = inspr_insp_regn.insp_regn_id AND insp_regn.insp_regn_id = inspr_insp_regn.insp_regn_id AND addr.cnty = cnty.cnty ");
strSql.Append(@" AND lic.clnt_cde = clnt.clnt_cde AND clnt.clnt_cde_prnt = '210' and insp_hist.insp_hist_id = insp_vst.insp_hist_id ");
strSql.Append(@" AND insp_vst.insp_vst_end_dte = TO_DATE ('");
strSql.Append(strLicenseType);
strSql.Append(@"', 'MM/DD/YYYY') AND insp_typ_defn.insp_typ_defn_id = insp_hist.insp_typ_defn_id AND insp_vst.inspr_id = inspr.inspr_id AND stff.stff_oper_id = inspr.stff_oper_id AND insp_disp_typ.insp_disp_typ_id = insp_hist.insp_disp_typ_id) a ");
app.Diagnostics.WriteIf(Diagnostics.DiagnosticsLevel.Verbose, string.Format("Sql Query: {0}", strSql.ToString()));
using (OdbcConnection con = new OdbcConnection(connectionString))
{
try
{
con.Open();
using (OdbcCommand command = new OdbcCommand(strSql.ToString(), con))
using (OdbcDataReader reader = command.ExecuteReader())
{
if (reader.HasRows)
{
string strLicType = "";
string strInspID = "";
string strLicenseNum = "";
string strFileNum = "";
string strKeyName = "";
string strDBAName = "";
string strInDorgNum = "";
string strVisitNum = "";
string strDisposition = "";
//string strInspDate = "";
string strCity = "";
string strCounty = "";
string strRegion = "";
string strInspector = "";
//string strInspNum = "";
string strSubject = "";
string strInspType = "";
reader.Read();
strLicType = reader["lic_Type"].ToString();
strInspID = reader["insp_vst_id"].ToString();
strLicenseNum = reader["lic_Num"].ToString();
strFileNum = reader["file_Num"].ToString();
strKeyName = reader["key_Name"].ToString();
strDBAName = reader["dba_kw"].ToString();
strInDorgNum = reader["indorg_num"].ToString();
strVisitNum = reader["visit_Num"].ToString();
//strInspDate = reader["visit_date"].ToString();
strDisposition = reader["disposition_kw"].ToString();
strCity = reader["city_kw"].ToString();
strInspector = reader["inspector_name"].ToString();
//strInspNum = reader["insp_nbr"].ToString();
strSubject = reader["key_Name"].ToString();
strInspType = reader["insp_typ"].ToString();
if (reader["county_kw"] != DBNull.Value)
{
strCounty = reader["county_kw"].ToString();
}
else
{
strCounty = "Not Available";
}
if (reader["region_kw"] != DBNull.Value)
{
strRegion = reader["region_kw"].ToString();
}
else
{
strRegion = "Not Available";
}
Keyword kwdLicType = null;
if (!String.IsNullOrEmpty(strLicType))
{
KeywordType kwtLicType = app.Core.KeywordTypes.Find(gSaveToLicType);
if (kwtLicType != null)
{
kwdLicType = CreateKeywordHelper(kwtLicType, strLicType);
}
}
Keyword kwdLicenseNum = null;
if (!String.IsNullOrEmpty(strLicenseNum))
{
KeywordType kwtLicenseNum = app.Core.KeywordTypes.Find(gSaveToLicNum);
if (kwtLicenseNum != null)
{
kwdLicenseNum = CreateKeywordHelper(kwtLicenseNum, strLicenseNum);
}
}
Keyword kwdFileNum = null;
if (!String.IsNullOrEmpty(strFileNum))
{
KeywordType kwtFileNum = app.Core.KeywordTypes.Find(gSaveToFileNum);
if (kwtFileNum != null)
{
kwdFileNum = CreateKeywordHelper(kwtFileNum, strFileNum);
}
}
Keyword kwdKeyName = null;
if (!String.IsNullOrEmpty(strKeyName))
{
KeywordType kwtKeyName = app.Core.KeywordTypes.Find(gSaveToKeyName);
if (kwtKeyName != null)
{
kwdKeyName = CreateKeywordHelper(kwtKeyName, strKeyName);
}
}
Keyword kwdSubject = null;
if (!String.IsNullOrEmpty(strSubject))
{
KeywordType kwtSubject = app.Core.KeywordTypes.Find(gSaveToSubject);
if (kwtSubject != null)
{
kwdSubject = CreateKeywordHelper(kwtSubject, strSubject);
}
}
Keyword kwdDBAName = null;
if (!String.IsNullOrEmpty(strDBAName))
{
KeywordType kwtDBAName = app.Core.KeywordTypes.Find(gSaveToDBA);
if (kwtDBAName != null)
{
kwdDBAName = CreateKeywordHelper(kwtDBAName, strDBAName);
}
}
Keyword kwdInDorgNum = null;
if (!String.IsNullOrEmpty(strInDorgNum))
{
KeywordType kwtInDorgNum = app.Core.KeywordTypes.Find(gSaveToIndOrgNum);
if (kwtInDorgNum != null)
{
kwdInDorgNum = CreateKeywordHelper(kwtInDorgNum, strInDorgNum);
}
}
Keyword kwdVisitNum = null;
if (!String.IsNullOrEmpty(strVisitNum))
{
KeywordType kwtVisitNum = app.Core.KeywordTypes.Find(gSaveToVisitNum);
if (kwtVisitNum != null)
{
kwdVisitNum = CreateKeywordHelper(kwtVisitNum, strVisitNum);
}
}
Keyword kwdDisposition = null;
if (!String.IsNullOrEmpty(strDisposition))
{
KeywordType kwtDisposition = app.Core.KeywordTypes.Find(gSaveToLicType);
if (kwtDisposition != null)
{
kwdDisposition = CreateKeywordHelper(kwtDisposition, strDisposition);
}
}
Keyword kwdCity = null;
if (!String.IsNullOrEmpty(strCity))
{
KeywordType kwtCity = app.Core.KeywordTypes.Find(gSaveToCity);
if (kwtCity != null)
{
kwdCity = CreateKeywordHelper(kwtCity, strCity);
}
}
Keyword kwdCounty = null;
if (!String.IsNullOrEmpty(strCounty))
{
KeywordType kwtCounty = app.Core.KeywordTypes.Find(gSaveToCounty);
if (kwtCounty != null)
{
kwdCounty = CreateKeywordHelper(kwtCounty, strCounty);
}
}
Keyword kwdRegion = null;
if (!String.IsNullOrEmpty(strRegion))
{
KeywordType kwtRegion = app.Core.KeywordTypes.Find(gSaveToRegion);
if (kwtRegion != null)
{
kwdRegion = CreateKeywordHelper(kwtRegion, strRegion);
}
}
Keyword kwdInspector = null;
if (!String.IsNullOrEmpty(strInspector))
{
KeywordType kwtInspector = app.Core.KeywordTypes.Find(gSaveToInspectorName);
if (kwtInspector != null)
{
kwdInspector = CreateKeywordHelper(kwtInspector, strInspector);
}
}
Keyword kwdInspectorID = null;
if (!String.IsNullOrEmpty(strInspID))
{
KeywordType kwtInspNum = app.Core.KeywordTypes.Find(gSaveToInspectionID);
if (kwtInspNum != null)
{
kwdInspectorID = CreateKeywordHelper(kwtInspNum, strInspID);
}
}
Keyword kwdInspTypeDesc = null;
if (!String.IsNullOrEmpty(strInspType))
{
KeywordType kwtInspTypeDesc = app.Core.KeywordTypes.Find(gSaveToInspTypeDesc);
if (kwtInspTypeDesc != null)
{
kwdInspTypeDesc = CreateKeywordHelper(kwtInspTypeDesc, strInspType);
}
}
using (DocumentLock documentLock = _currentDocument.LockDocument())
{
// Ensure lock was obtained
if (documentLock.Status != DocumentLockStatus.LockObtained)
{
throw new Exception("Document lock not obtained");
}
// Create keyword modifier object to hold keyword changes
KeywordModifier keyModifier = _currentDocument.CreateKeywordModifier();
// Add update keyword call to keyword modifier object
//Note Overloads available for use
//(I.E.): keyModifier.AddKeyword(keywordTypeName,keywordValue)
if (kwdLicType != null)
{
keyModifier.AddKeyword(kwdLicType);
}
if (kwdLicenseNum != null)
{
keyModifier.AddKeyword(kwdLicenseNum);
}
if (kwdInspectorID != null)
{
keyModifier.AddKeyword(kwdInspectorID);
}
if (kwdFileNum != null)
{
keyModifier.AddKeyword(kwdFileNum);
}
if (kwdKeyName != null)
{
keyModifier.AddKeyword(kwdKeyName);
}
if (kwdDBAName != null)
{
keyModifier.AddKeyword(kwdDBAName);
}
if (kwdInDorgNum != null)
{
keyModifier.AddKeyword(kwdInDorgNum);
}
if (kwdVisitNum != null)
{
keyModifier.AddKeyword(kwdVisitNum);
}
if (kwdDisposition != null)
{
keyModifier.AddKeyword(kwdDisposition);
}
if (kwdCity != null)
{
keyModifier.AddKeyword(kwdCity);
}
if (kwdCounty != null)
{
keyModifier.AddKeyword(kwdCounty);
}
if (kwdRegion != null)
{
keyModifier.AddKeyword(kwdRegion);
}
if (kwdInspector != null)
{
keyModifier.AddKeyword(kwdInspector);
}
if (kwdInspTypeDesc != null)
{
keyModifier.AddKeyword(kwdInspTypeDesc);
}
if (kwdSubject != null)
{
keyModifier.AddKeyword(kwdSubject);
}
// Apply keyword change to the document
keyModifier.ApplyChanges();
string output = String.Format("Keyword: '{0}' Value: '{1}', {33}Keyword: '{2}' Value: '{3}', {33}Keyword: '{4}' Value: '{5}', {33}Keyword: '{6}' Value: '{7}'," +
"{33}Keyword: '{8}' Value: '{9}', {33}Keyword: '{10}' Value: '{11}', {33}Keyword: '{12}' Value: '{13}', {33}Keyword: '{14}' Value: '{15}', {33}Keyword: '{16}' Value: '{17}'," +
"{33}Keyword: '{18}' Value: '{19}', {33}Keyword: '{20}' Value: '{21}', {33}Keyword: '{22}' Value: '{23}', {33}Keyword: '{24}' Value: '{25}', {33}Keyword: '{26}' Value: '{27}'," +
"{33}Keyword: '{28}' Value: '{29}', {33}Keyword: '{30}' Value: '{31}', {33}added to Document {32}.",
gSaveToLicNum, strLicNum, gSaveToLicType, strLicType, gSaveToInspectionID, strInspID, gSaveToFileNum, strFileNum, gSaveToKeyName, strKeyName, gSaveToSubject, strSubject,
gSaveToDBA, strDBAName, gSaveToIndOrgNum, strInDorgNum, gSaveToVisitNum, strVisitNum, gSaveToDisposition, strDisposition, gSaveToCity, strCity, gSaveToCounty, strCounty,
gSaveToRegion, strRegion, gSaveToInspectorName, strInspector, gSaveToInspTypeDesc, strInspType, gSaveToInspNum, "Not being returned", _currentDocument.ID, Environment.NewLine);
//Output the results to the OnBase Diagnostics Console
app.Diagnostics.WriteIf(Hyland.Unity.Diagnostics.DiagnosticsLevel.Verbose, output);
documentLock.Release();
}
}
else
{
throw new Exception(string.Format("No records found in database"));
}
}
}
catch (Exception ex)
{
throw new ApplicationException("Error during database operations!", ex);
}
finally
{
if (con.State == ConnectionState.Open)
{
con.Close();
}
}
}
}
catch (Exception ex)
{
// Handle exceptions and log to Diagnostics Console and document history
HandleException(ex, ref app, ref args);
}
finally
{
// Log script execution end
app.Diagnostics.WriteIf(Diagnostics.DiagnosticsLevel.Info,
string.Format("End Script - [{0}]", ScriptName));
}
}
/// <summary>
///
/// select a set of rows from table t_RBSR_AUFW_u_Entitlement.
/// </summary>
/// <param name="maxRowsToReturn">Max number of rows to return. If null or 0 all rows are returned.</param>
/// <param name="extendedCriteria">Statement appended to the end of the WHERE-clause</param>
/// <param name="extentedParameters">Values bound to the query (?) marks in <code>extendedCriteria</code></param>
/// <param name="extendedSortOrder">Statement appended to the end of the ORDER BY-clause</param>
/// <returns>returnListEntitlement[]</returns>
public returnListEntitlement[] ListEntitlement(int?maxRowsToReturn, string extendedCriteria, string[] extendedParameters, string extendedSortOrder)
{
returnListEntitlement[] rv = null;
DBConnect();
OdbcCommand cmd = _dbConnection.CreateCommand();
string runTimeCriteria = (extendedCriteria != null ? extendedCriteria.ToLower() : string.Empty).Replace("\"id\"", "\"c_id\"").Replace("\"standardactivity\"", "\"c_u_StandardActivity\"").Replace("\"roletype\"", "\"c_u_RoleType\"").Replace("\"application\"", "\"c_u_Application\"").Replace("\"system\"", "\"c_u_System\"").Replace("\"platform\"", "\"c_u_Platform\"").Replace("\"entitlementname\"", "\"c_u_EntitlementName\"").Replace("\"entitlementvalue\"", "\"c_u_EntitlementValue\"").Replace("\"authobjname\"", "\"c_u_AuthObjName\"").Replace("\"authobjvalue\"", "\"c_u_AuthObjValue\"").Replace("\"fieldsecname\"", "\"c_u_FieldSecName\"").Replace("\"fieldsecvalue\"", "\"c_u_FieldSecValue\"").Replace("\"level4secname\"", "\"c_u_Level4SecName\"").Replace("\"level4secvalue\"", "\"c_u_Level4SecValue\"").Replace("\"commentary\"", "\"c_u_Commentary\"").Replace("\"genmanifestvalue\"", "\"c_u_GENmanifestValue\"").Replace("\"checksum\"", "\"c_u_CHECKSUM\"").Replace("\"status\"", "\"c_u_Status\"");
string runTimeSortOrder = (extendedSortOrder != null ? extendedSortOrder.ToLower() : string.Empty).Replace("\"id\"", "\"c_id\"").Replace("\"standardactivity\"", "\"c_u_StandardActivity\"").Replace("\"roletype\"", "\"c_u_RoleType\"").Replace("\"application\"", "\"c_u_Application\"").Replace("\"system\"", "\"c_u_System\"").Replace("\"platform\"", "\"c_u_Platform\"").Replace("\"entitlementname\"", "\"c_u_EntitlementName\"").Replace("\"entitlementvalue\"", "\"c_u_EntitlementValue\"").Replace("\"authobjname\"", "\"c_u_AuthObjName\"").Replace("\"authobjvalue\"", "\"c_u_AuthObjValue\"").Replace("\"fieldsecname\"", "\"c_u_FieldSecName\"").Replace("\"fieldsecvalue\"", "\"c_u_FieldSecValue\"").Replace("\"level4secname\"", "\"c_u_Level4SecName\"").Replace("\"level4secvalue\"", "\"c_u_Level4SecValue\"").Replace("\"commentary\"", "\"c_u_Commentary\"").Replace("\"genmanifestvalue\"", "\"c_u_GENmanifestValue\"").Replace("\"checksum\"", "\"c_u_CHECKSUM\"").Replace("\"status\"", "\"c_u_Status\"");
if (maxRowsToReturn.HasValue && maxRowsToReturn.Value > 0)
{
if (_dbConnection.Driver.ToLower().StartsWith("sqlsrv"))
{
cmd.CommandText = "SELECT TOP " + maxRowsToReturn.Value + " \"c_id\", \"c_u_StandardActivity\", \"c_u_RoleType\", \"c_u_System\", \"c_u_Platform\", \"c_u_EntitlementName\", \"c_u_EntitlementValue\", \"c_u_AuthObjName\", \"c_u_AuthObjValue\", \"c_u_FieldSecName\", \"c_u_FieldSecValue\", \"c_u_Level4SecName\", \"c_u_Level4SecValue\", \"c_u_Commentary\", \"c_u_GENmanifestValue\", \"c_u_Application\", \"c_u_CHECKSUM\", \"c_u_Status\" FROM \"t_RBSR_AUFW_u_Entitlement\"" + (string.IsNullOrEmpty(runTimeCriteria) ? "" : " where (" + runTimeCriteria + ")") + (string.IsNullOrEmpty(runTimeSortOrder) ? "" : " order by " + runTimeSortOrder);
}
else
{
cmd.CommandText = "SELECT \"c_id\", \"c_u_StandardActivity\", \"c_u_RoleType\", \"c_u_System\", \"c_u_Platform\", \"c_u_EntitlementName\", \"c_u_EntitlementValue\", \"c_u_AuthObjName\", \"c_u_AuthObjValue\", \"c_u_FieldSecName\", \"c_u_FieldSecValue\", \"c_u_Level4SecName\", \"c_u_Level4SecValue\", \"c_u_Commentary\", \"c_u_GENmanifestValue\", \"c_u_Application\", \"c_u_CHECKSUM\", \"c_u_Status\" FROM \"t_RBSR_AUFW_u_Entitlement\"" + (string.IsNullOrEmpty(runTimeCriteria) ? "" : " where (" + runTimeCriteria + ")") + (string.IsNullOrEmpty(runTimeSortOrder) ? "" : " order by " + runTimeSortOrder) + " LIMIT " + maxRowsToReturn.Value;
}
}
else
{
cmd.CommandText = "SELECT \"c_id\", \"c_u_StandardActivity\", \"c_u_RoleType\", \"c_u_System\", \"c_u_Platform\", \"c_u_EntitlementName\", \"c_u_EntitlementValue\", \"c_u_AuthObjName\", \"c_u_AuthObjValue\", \"c_u_FieldSecName\", \"c_u_FieldSecValue\", \"c_u_Level4SecName\", \"c_u_Level4SecValue\", \"c_u_Commentary\", \"c_u_GENmanifestValue\", \"c_u_Application\", \"c_u_CHECKSUM\", \"c_u_Status\" FROM \"t_RBSR_AUFW_u_Entitlement\"" + (string.IsNullOrEmpty(runTimeCriteria) ? "" : " where (" + runTimeCriteria + ")") + (string.IsNullOrEmpty(runTimeSortOrder) ? "" : " order by " + runTimeSortOrder);
}
for (int i = 0; i < extendedParameters.Length; i++)
{
cmd.Parameters.Add("@ExtendedParam_" + i.ToString(), OdbcType.NVarChar);
cmd.Parameters["@ExtendedParam_" + i.ToString()].Value = (object)extendedParameters[i];
}
OdbcDataReader dr = cmd.ExecuteReader();
List <returnListEntitlement> rvl = new List <returnListEntitlement>();
while (dr.Read())
{
returnListEntitlement cr = new returnListEntitlement();
if (dr.IsDBNull(0))
{
throw new Exception("Value 'null' is not allowed for 'ID'");
}
else
{
cr.ID = dr.GetInt32(0);
}
if (dr.IsDBNull(1))
{
throw new Exception("Value 'null' is not allowed for 'StandardActivity'");
}
else
{
cr.StandardActivity = dr.GetString(1);
}
if (dr.IsDBNull(2))
{
throw new Exception("Value 'null' is not allowed for 'RoleType'");
}
else
{
cr.RoleType = dr.GetString(2);
}
if (dr.IsDBNull(3))
{
throw new Exception("Value 'null' is not allowed for 'System'");
}
else
{
cr.System = dr.GetString(3);
}
if (dr.IsDBNull(4))
{
throw new Exception("Value 'null' is not allowed for 'Platform'");
}
else
{
cr.Platform = dr.GetString(4);
}
if (dr.IsDBNull(5))
{
throw new Exception("Value 'null' is not allowed for 'EntitlementName'");
}
else
{
cr.EntitlementName = dr.GetString(5);
}
if (dr.IsDBNull(6))
{
throw new Exception("Value 'null' is not allowed for 'EntitlementValue'");
}
else
{
cr.EntitlementValue = dr.GetString(6);
}
if (dr.IsDBNull(7))
{
cr.AuthObjName = null;
}
else
{
cr.AuthObjName = dr.GetString(7);
}
if (dr.IsDBNull(8))
{
cr.AuthObjValue = null;
}
else
{
cr.AuthObjValue = dr.GetString(8);
}
if (dr.IsDBNull(9))
{
cr.FieldSecName = null;
}
else
{
cr.FieldSecName = dr.GetString(9);
}
if (dr.IsDBNull(10))
{
cr.FieldSecValue = null;
}
else
{
cr.FieldSecValue = dr.GetString(10);
}
if (dr.IsDBNull(11))
{
cr.Level4SecName = null;
}
else
{
cr.Level4SecName = dr.GetString(11);
}
if (dr.IsDBNull(12))
{
cr.Level4SecValue = null;
}
else
{
cr.Level4SecValue = dr.GetString(12);
}
if (dr.IsDBNull(13))
{
cr.Commentary = null;
}
else
{
cr.Commentary = dr.GetString(13);
}
if (dr.IsDBNull(14))
{
cr.GENmanifestValue = null;
}
else
{
cr.GENmanifestValue = dr.GetString(14);
}
if (dr.IsDBNull(15))
{
throw new Exception("Value 'null' is not allowed for 'Application'");
}
else
{
cr.Application = dr.GetString(15);
}
if (dr.IsDBNull(16))
{
throw new Exception("Value 'null' is not allowed for 'CHECKSUM'");
}
else
{
cr.CHECKSUM = dr.GetString(16);
}
if (dr.IsDBNull(17))
{
cr.Status = null;
}
else
{
cr.Status = dr.GetString(17);
}
rvl.Add(cr);
}
dr.Close();
dr.Dispose();
rv = rvl.ToArray();
cmd.Dispose();
DBClose();
return(rv);
}
/// <summary>
/// Implementation of <see cref="IWorkflowScript.OnWorkflowScriptExecute" />.
/// <seealso cref="IWorkflowScript" />
/// </summary>
/// <param name="app">Unity Application object</param>
/// <param name="args">Workflow event arguments</param>
public void OnWorkflowScriptExecute(Application app, WorkflowEventArgs args)
// public void OnWorkflowScriptExecute(Application app, WorkflowEventArgs args=null)
{
try
{
// Initialize global settings
IntializeScript(ref app, ref args);
string strRelatedCase = "";
args.SessionPropertyBag.TryGetValue(gPropCase, out strRelatedCase);
app.Diagnostics.Write(string.Format("property for related case # = {0}", strRelatedCase));
if (string.IsNullOrEmpty(strRelatedCase))
{
throw new Exception(String.Format("Property '{0}' not found", strRelatedCase));
}
//access Config Item for OnBase User
string gUSER = "";
if (app.Configuration.TryGetValue("OnBaseUser", out gUSER))
{
}
//access Config Item for OnBase Password
string gPASS = "";
if (app.Configuration.TryGetValue("OnBasePassword", out gPASS))
{
}
/* COMMENT THIS SECTION OUT WHEN MOVING TO PROD */
//access Config Item for OnBase UAT ODBC
string gODBC = "";
if (app.Configuration.TryGetValue("OBUAT", out gODBC))
{
}
/* UNCOMMENT THIS SECTION WHEN MOVING TO PROD
* //access Config Item for OnBase PROD ODBC
* string gODBC = "";
* if (app.Configuration.TryGetValue("OnBasePROD", out gODBC))
* {
* }
*/
string connectionString = string.Format("DSN={0};Uid={1};Pwd={2};", gODBC, gUSER, gPASS);
app.Diagnostics.WriteIf(Diagnostics.DiagnosticsLevel.Verbose, string.Format("Connection string: {0}", connectionString));
StringBuilder strSql = new StringBuilder();
strSql.Append("select distinct ua.username AS USER FROM hsi.itemlc ilc ");
strSql.Append("join hsi.itemlcxuser ilcxu on ilc.itemnum = ilcxu.itemnum ");
strSql.Append("join hsi.useraccount ua on ilcxu.usernum = ua.usernum ");
strSql.Append("join hsi.keyxitem138 ki138 on ilc.itemnum = ki138.itemnum ");
strSql.Append("join hsi.keytable138 kt138 on ki138.keywordnum = kt138.keywordnum ");
strSql.Append("where ilc.lcnum = 177 and ilc.statenum = 568 and kt138.keyvaluechar = '");
strSql.Append(strRelatedCase);
strSql.Append("'");
app.Diagnostics.WriteIf(Diagnostics.DiagnosticsLevel.Verbose, string.Format("Sql Query: {0}", strSql.ToString()));
using (OdbcConnection con = new OdbcConnection(connectionString))
{
try
{
con.Open();
using (OdbcCommand command = new OdbcCommand(strSql.ToString(), con))
using (OdbcDataReader reader = command.ExecuteReader())
{
if (reader.HasRows)
{
string strProf = "";
reader.Read();
strProf = reader["USER"].ToString();
Keyword kwdProf = null;
if (!String.IsNullOrEmpty(strProf))
{
KeywordType kwtProf = app.Core.KeywordTypes.Find(gSaveToReassignedAnalyst);
if (kwtProf != null)
{
kwdProf = CreateKeywordHelper(kwtProf, strProf);
}
}
using (DocumentLock documentLock = _currentDocument.LockDocument())
{
// Ensure lock was obtained
if (documentLock.Status != DocumentLockStatus.LockObtained)
{
throw new Exception("Document lock not obtained");
}
// Create keyword modifier object to hold keyword changes
KeywordModifier keyModifier = _currentDocument.CreateKeywordModifier();
// Add update keyword call to keyword modifier object
//Note Overloads available for use
//(I.E.): keyModifier.AddKeyword(keywordTypeName,keywordValue)
if (kwdProf != null)
{
keyModifier.AddKeyword(kwdProf);
}
// Apply keyword change to the document
keyModifier.ApplyChanges();
string output = String.Format("Keyword: '{0}' Value: '{1}', {3}added to Document {2}.",
gSaveToReassignedAnalyst, strProf, _currentDocument.ID, Environment.NewLine);
//Output the results to the OnBase Diagnostics Console
app.Diagnostics.WriteIf(Hyland.Unity.Diagnostics.DiagnosticsLevel.Verbose, output);
documentLock.Release();
}
}
else
{
throw new Exception(string.Format("No records found in database for {0}='{1}'", gPropCase, strRelatedCase));
}
}
}
catch (Exception ex)
{
throw new ApplicationException("Error during database operations!", ex);
}
finally
{
if (con.State == ConnectionState.Open)
{
con.Close();
}
}
}
}
catch (Exception ex)
{
// Handle exceptions and log to Diagnostics Console and document history
HandleException(ex, ref app, ref args);
}
finally
{
// Log script execution end
app.Diagnostics.WriteIf(Diagnostics.DiagnosticsLevel.Info,
string.Format("End Script - [{0}]", ScriptName));
}
}
private void BindData()
{
string by = drp_by.SelectedItem.Text;
string val = txt_val.Text.Trim();
if (by == "Asset Code")
{
OdbcCommand cmd = conn_asset.CreateCommand();
cmd.CommandText = "select p_no,openingDate,closingDate,userDescription,systemDescription,partRepaired from ast_call where astCode='" + val + "' order by openingDate desc";
conn_asset.Open();
cmd.CommandType = CommandType.Text;
DataTable dt = new DataTable();
DataRow newRow;
OdbcDataReader dr = cmd.ExecuteReader();
dt.Columns.Add(new System.Data.DataColumn("p_no", typeof(String)));
dt.Columns.Add(new System.Data.DataColumn("openingDate", typeof(DateTime)));
dt.Columns.Add(new System.Data.DataColumn("closingDate", typeof(DateTime)));
dt.Columns.Add(new System.Data.DataColumn("userDescription", typeof(String)));
dt.Columns.Add(new System.Data.DataColumn("systemDescription", typeof(String)));
dt.Columns.Add(new System.Data.DataColumn("partRepaired", typeof(String)));
while (dr.Read())
{
newRow = dt.NewRow();
newRow["p_no"] = Convert.ToString(dr["p_no"]);
newRow["openingDate"] = Convert.ToDateTime(dr["openingDate"]);
newRow["closingDate"] = Convert.ToDateTime(dr["closingDate"]);
newRow["userDescription"] = Convert.ToString(dr["userDescription"]);
newRow["systemDescription"] = Convert.ToString(dr["systemDescription"]);
newRow["partRepaired"] = Convert.ToString(dr["partRepaired"]);
dt.Rows.Add(newRow);
}
if (dt.Rows.Count > 0)
{
grid_astcode.Visible = true;
grid_pno.Visible = false;
grid_all.Visible = false;
grid_astcode.DataSource = dt;
grid_astcode.DataBind();
lbl_no_recs.Visible = false;
}
else
{
grid_astcode.Visible = false;
grid_pno.Visible = false;
grid_all.Visible = false;
lbl_no_recs.Visible = true;
}
conn_asset.Close();
}
//****************************************
if (by == "Personal No.")
{
OdbcCommand cmd = conn_asset.CreateCommand();
cmd.CommandText = "select astCode,openingDate,closingDate,userDescription,systemDescription,partRepaired from ast_call where p_no='" + val + "' order by openingDate desc ";
conn_asset.Open();
cmd.CommandType = CommandType.Text;
DataTable dt = new DataTable();
DataRow newRow;
OdbcDataReader dr = cmd.ExecuteReader();
dt.Columns.Add(new System.Data.DataColumn("astCode", typeof(String)));
dt.Columns.Add(new System.Data.DataColumn("openingDate", typeof(DateTime)));
dt.Columns.Add(new System.Data.DataColumn("closingDate", typeof(DateTime)));
dt.Columns.Add(new System.Data.DataColumn("userDescription", typeof(String)));
dt.Columns.Add(new System.Data.DataColumn("systemDescription", typeof(String)));
dt.Columns.Add(new System.Data.DataColumn("partRepaired", typeof(String)));
while (dr.Read())
{
newRow = dt.NewRow();
newRow["astCode"] = Convert.ToString(dr["astCode"]);
newRow["openingDate"] = Convert.ToDateTime(dr["openingDate"]);
newRow["closingDate"] = Convert.ToDateTime(dr["closingDate"]);
newRow["userDescription"] = Convert.ToString(dr["userDescription"]);
newRow["systemDescription"] = Convert.ToString(dr["systemDescription"]);
newRow["partRepaired"] = Convert.ToString(dr["partRepaired"]);
dt.Rows.Add(newRow);
}
if (dt.Rows.Count > 0)
{
grid_pno.Visible = true;
grid_astcode.Visible = false;
grid_all.Visible = false;
grid_pno.DataSource = dt;
grid_pno.DataBind();
lbl_no_recs.Visible = false;
}
else
{
grid_pno.Visible = false;
grid_astcode.Visible = false;
grid_all.Visible = false;
lbl_no_recs.Visible = true;
}
conn_asset.Close();
}
//****************************************
//*******************************
}
private void Btn_guardar_Click(object sender, EventArgs e)
{
string fecha = DateTime.Now.ToString("yyyy-MM-dd");
// MessageBox.Show(fecha);
string scodigoProveedor = "";
bool validacionProveedor = false;
//validacion de combobox vacio
if (Cbo_proveedor.SelectedItem != null)
{
string snombreProveedor = Cbo_proveedor.SelectedItem.ToString();
//consulta el codigo del proveedor obteniendo el texto del combobox con el nombre del proveedor
try
{
OdbcCommand sql = new OdbcCommand("Select PK_IdProveedores from tbl_proveedores where nombre_proveedor = '" + snombreProveedor + "' and Estado_proveedor = 1", conexion.conectar());
OdbcDataReader almacena = sql.ExecuteReader();
while (almacena.Read() == true)
{
scodigoProveedor = almacena.GetString(0);
}
almacena.Close();
conexion.cerrarConexion();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
else
{
validacionProveedor = true;
MessageBox.Show("Debe seleccionar un proveedor para el registro de la orden de compra");
Cbo_proveedor.Focus();
}
if (validacionProveedor == false)
{
//validar en el grid que no hayan cantidades vacias
if (validacionColumnaCantidad())
{
MessageBox.Show("Debe ingresar una cantidad de productos para la cotizacion");
}
else
{
//valida que hayan datos en el grid para poder guardarlos en la bd
if (dgv_ordenCompra.Rows.Count > 0)
{
try
{
string scodigoOrdenCompra = Txt_noOrdenCompra.Text;
string stotalOrdenCompra = Txt_total.Text;
OdbcCommand sql = new OdbcCommand("INSERT INTO tbl_orden_compra_encabezado (PK_IdComprasEncabezado, PK_IdProveedores, fecha_pedido, total)" +
" VALUES( " + scodigoOrdenCompra + ", " + scodigoProveedor + ", '" + fecha + "' , " + stotalOrdenCompra + " )", conexion.conectar());
sql.ExecuteNonQuery();
conexion.cerrarConexion();
try
{
sql = new OdbcCommand("INSERT INTO tbl_compras (PK_IdComprasEncabezado, fecha_compra, fecha_modificacion, Tracking_compra, estado_compra)" +
" VALUES( " + scodigoOrdenCompra + ", '" + fecha + "', '" + fecha + "' , 'En espera de respuesta por el proveedor', '1')", conexion.conectar());
sql.ExecuteNonQuery();
conexion.cerrarConexion();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
try
{
foreach (DataGridViewRow row in dgv_ordenCompra.Rows)
{
try
{
string scodigoOrdenCompra = Txt_noOrdenCompra.Text;
if (row.Cells[0].Value != null)
{
OdbcCommand sql = new OdbcCommand("INSERT INTO tbl_orden_compra_detalle (PK_IdComprasEncabezado, PK_IdProducto, Precio, Cantidad)" +
" VALUES ( " + scodigoOrdenCompra + ", " + Convert.ToString(row.Cells[0].Value) + ", " + Convert.ToString(row.Cells[3].Value) + ", " + Convert.ToString(row.Cells[4].Value) + " )", conexion.conectar());
sql.ExecuteNonQuery();
conexion.cerrarConexion();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
MessageBox.Show("Orden de compra con el codigo: " + Txt_noOrdenCompra.Text + " almacenada exitosamente!");
limpiarForm();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
else
{
MessageBox.Show("No se puede generar una orden de compra sin productos en listados");
}
}
}
}
public void SendEMail(string Status, int UserCheck)
{
string connectionString = ConfigurationManager.ConnectionStrings["PetroneedsConnectionString"].ConnectionString;
EMPLOYEE_NO = Request.QueryString["EMPLOYEE_NO"];
DEPARTMENT_NO = Request.QueryString["DEP_NO"];
ADMINI = Request.QueryString["ADMIN"];
MANAGER = Request.QueryString["MNG"];
HRM = Request.QueryString["HR"];
string date;
date = DateTime.Now.Date.ToString("dd-MMMM-yyyy");
// Requester
string sql = "SELECT USERS_INFORMATIONS.EMAIL, USERS_INFORMATIONS.FULL_USER_NAME, DEPARTMENTS.DEP_NAME " +
"FROM USERS_INFORMATIONS, DEPARTMENTS WHERE DEPARTMENTS.DEP_NO = USERS_INFORMATIONS.DEP_NO AND " +
"USERS_INFORMATIONS.EMPLOYEE_NO = '" + TextBox2.Text + "'";
//adminstrati
string sql2 = "SELECT USERS_INFORMATIONS.EMAIL, USERS_INFORMATIONS.FULL_USER_NAME, DEPARTMENTS.DEP_NAME " +
"FROM USERS_INFORMATIONS, DEPARTMENTS WHERE DEPARTMENTS.DEP_NO = USERS_INFORMATIONS.DEP_NO AND " +
"USERS_INFORMATIONS.EMPLOYEE_NO = '" + ADMINI + "'";
// Manager
string sql3 = "SELECT USERS_INFORMATIONS.EMAIL, USERS_INFORMATIONS.FULL_USER_NAME, DEPARTMENTS.DEP_NAME " +
"FROM USERS_INFORMATIONS, DEPARTMENTS WHERE DEPARTMENTS.DEP_NO = USERS_INFORMATIONS.DEP_NO AND " +
"USERS_INFORMATIONS.EMPLOYEE_NO = '" + MANAGER + "'";
// HRM
string sql5 = "SELECT USERS_INFORMATIONS.EMAIL, USERS_INFORMATIONS.FULL_USER_NAME, DEPARTMENTS.DEP_NAME " +
"FROM USERS_INFORMATIONS, DEPARTMENTS WHERE DEPARTMENTS.DEP_NO = USERS_INFORMATIONS.DEP_NO AND " +
"USERS_INFORMATIONS.EMPLOYEE_NO = '" + HRM + "'";
OdbcConnection conn = new OdbcConnection(connectionString);
OdbcCommand cmd = new OdbcCommand(sql, conn);
OdbcCommand cmd2 = new OdbcCommand(sql2, conn);
OdbcCommand cmd3 = new OdbcCommand(sql3, conn);
OdbcCommand cmd5 = new OdbcCommand(sql5, conn);
cmd.Connection.Open();
OdbcDataReader read = cmd.ExecuteReader();
OdbcDataReader read2 = cmd2.ExecuteReader();
OdbcDataReader read3 = cmd3.ExecuteReader();
OdbcDataReader read5 = cmd5.ExecuteReader();
read.Read();
read2.Read();
read3.Read();
read5.Read();
if (read.HasRows) // Requester
{
EmpName = read["FULL_USER_NAME"].ToString();
EmpEmail = read["EMAIL"].ToString();
Depart = read["DEP_NAME"].ToString();
}
if (read2.HasRows) // Admin
{
EmpName2 = read2["FULL_USER_NAME"].ToString();
EmpEmail2 = read2["EMAIL"].ToString();
}
if (read3.HasRows) // Manager
{
EmpName3 = read3["FULL_USER_NAME"].ToString();
EmpEmail3 = read3["EMAIL"].ToString();
}
if (read5.HasRows)// HR
{
EmpName5 = read5["FULL_USER_NAME"].ToString();
EmpEmail5 = read5["EMAIL"].ToString();
}
if (UserCheck == 1) //Manager
{
Response.Redirect("~/UsersArea/E-mail.aspx?EMPLOYEE_NO=" + TextBox2.Text + "&DEP_NO=" + DEPARTMENT_NO + "&FULL_USER_NAME=" + EmpName + "&EMAIL=" + EmpEmail + "&DEP_NAME=" + Depart + "&REQSTATUS=" + Status + "&comment=" + TextBox1 + "&DATE=" + date + "" +
"&ApprovalName=" + EmpName5 + "&ApprovalEmail=" + EmpEmail5 + "&REQNAME=Business Card Request(BC)&AUTHORNAME=" + EmpName3 + "");
}
if (UserCheck == 2) // HR
{
Response.Redirect("~/UsersArea/E-mail.aspx?EMPLOYEE_NO=" + TextBox2.Text + "&DEP_NO=" + DEPARTMENT_NO + "&FULL_USER_NAME=" + EmpName + "&EMAIL=" + EmpEmail + "&DEP_NAME=" + Depart + "&REQSTATUS=" + Status + "&DATE=" + date + "" +
"&ApprovalName=" + EmpName2 + "&ApprovalEmail=" + EmpEmail2 + "&REQNAME= Business Card Request(BC)&AUTHORNAME=" + EmpName5 + "");
}
cmd.Connection.Close();
}
private void textBox1_TextChanged(object sender, EventArgs e)
{
if (textBox1.Text != "")
{
OdbcConnection conexion = ASG_DB.connectionResult();
try
{
dataGridView1.Rows.Clear();
string sql = string.Format("SELECT * FROM VISTA_DEVOLUCIONES WHERE ID_FACTURA LIKE '%{0}%' ORDER BY FECHA_DEVOLUCION DESC;", textBox1.Text.Trim());
OdbcCommand cmd = new OdbcCommand(sql, conexion);
OdbcDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
dataGridView1.Rows.Add(reader.GetString(0), reader.GetString(1), reader.GetString(2) + " " + reader.GetString(3), reader.GetString(4), reader.GetString(5), string.Format("Q.{0:###,###,###,##0.00##}", reader.GetDouble(6)), string.Format("Q.{0:###,###,###,##0.00##}", reader.GetDouble(7)), string.Format("Q.{0:###,###,###,##0.00##}", reader.GetDouble(8)), reader.GetString(9), reader.GetString(10), reader.GetString(11));
while (reader.Read())
{
dataGridView1.Rows.Add(reader.GetString(0), reader.GetString(1), reader.GetString(2) + " " + reader.GetString(3), reader.GetString(4), reader.GetString(5), string.Format("Q.{0:###,###,###,##0.00##}", reader.GetDouble(6)), string.Format("Q.{0:###,###,###,##0.00##}", reader.GetDouble(7)), string.Format("Q.{0:###,###,###,##0.00##}", reader.GetDouble(8)), reader.GetString(9), reader.GetString(10), reader.GetString(11));
}
}
else
{
dataGridView1.Rows.Clear();
sql = string.Format("SELECT * FROM VISTA_DEVOLUCIONES WHERE NOMBRE_CLIENTE LIKE '%{0}%' ORDER BY FECHA_DEVOLUCION DESC;", textBox1.Text.Trim());
cmd = new OdbcCommand(sql, conexion);
reader = cmd.ExecuteReader();
if (reader.Read())
{
dataGridView1.Rows.Add(reader.GetString(0), reader.GetString(1), reader.GetString(2) + " " + reader.GetString(3), reader.GetString(4), reader.GetString(5), string.Format("Q.{0:###,###,###,##0.00##}", reader.GetDouble(6)), string.Format("Q.{0:###,###,###,##0.00##}", reader.GetDouble(7)), string.Format("Q.{0:###,###,###,##0.00##}", reader.GetDouble(8)), reader.GetString(9), reader.GetString(10), reader.GetString(11));
while (reader.Read())
{
dataGridView1.Rows.Add(reader.GetString(0), reader.GetString(1), reader.GetString(2) + " " + reader.GetString(3), reader.GetString(4), reader.GetString(5), string.Format("Q.{0:###,###,###,##0.00##}", reader.GetDouble(6)), string.Format("Q.{0:###,###,###,##0.00##}", reader.GetDouble(7)), string.Format("Q.{0:###,###,###,##0.00##}", reader.GetDouble(8)), reader.GetString(9), reader.GetString(10), reader.GetString(11));
}
}
else
{
dataGridView1.Rows.Clear();
sql = string.Format("SELECT * FROM VISTA_DEVOLUCIONES WHERE APELLIDO_CLIENTE LIKE '%{0}%' ORDER BY FECHA_DEVOLUCION DESC;", textBox1.Text.Trim());
cmd = new OdbcCommand(sql, conexion);
reader = cmd.ExecuteReader();
if (reader.Read())
{
dataGridView1.Rows.Add(reader.GetString(0), reader.GetString(1), reader.GetString(2) + " " + reader.GetString(3), reader.GetString(4), reader.GetString(5), string.Format("Q.{0:###,###,###,##0.00##}", reader.GetDouble(6)), string.Format("Q.{0:###,###,###,##0.00##}", reader.GetDouble(7)), string.Format("Q.{0:###,###,###,##0.00##}", reader.GetDouble(8)), reader.GetString(9), reader.GetString(10), reader.GetString(11));
while (reader.Read())
{
dataGridView1.Rows.Add(reader.GetString(0), reader.GetString(1), reader.GetString(2) + " " + reader.GetString(3), reader.GetString(4), reader.GetString(5), string.Format("Q.{0:###,###,###,##0.00##}", reader.GetDouble(6)), string.Format("Q.{0:###,###,###,##0.00##}", reader.GetDouble(7)), string.Format("Q.{0:###,###,###,##0.00##}", reader.GetDouble(8)), reader.GetString(9), reader.GetString(10), reader.GetString(11));
}
}
else
{
dataGridView1.Rows.Clear();
sql = string.Format("SELECT * FROM VISTA_DEVOLUCIONES WHERE TIPO_DEVOLUCION LIKE '%{0}%' ORDER BY FECHA_DEVOLUCION DESC;", textBox1.Text.Trim());
cmd = new OdbcCommand(sql, conexion);
reader = cmd.ExecuteReader();
if (reader.Read())
{
dataGridView1.Rows.Add(reader.GetString(0), reader.GetString(1), reader.GetString(2) + " " + reader.GetString(3), reader.GetString(4), reader.GetString(5), string.Format("Q.{0:###,###,###,##0.00##}", reader.GetDouble(6)), string.Format("Q.{0:###,###,###,##0.00##}", reader.GetDouble(7)), string.Format("Q.{0:###,###,###,##0.00##}", reader.GetDouble(8)), reader.GetString(9), reader.GetString(10), reader.GetString(11));
while (reader.Read())
{
dataGridView1.Rows.Add(reader.GetString(0), reader.GetString(1), reader.GetString(2) + " " + reader.GetString(3), reader.GetString(4), reader.GetString(5), string.Format("Q.{0:###,###,###,##0.00##}", reader.GetDouble(6)), string.Format("Q.{0:###,###,###,##0.00##}", reader.GetDouble(7)), string.Format("Q.{0:###,###,###,##0.00##}", reader.GetDouble(8)), reader.GetString(9), reader.GetString(10), reader.GetString(11));
}
}
else
{
dataGridView1.Rows.Clear();
sql = string.Format("SELECT * FROM VISTA_DEVOLUCIONES WHERE FECHA_DEVOLUCION LIKE '%{0}%' ORDER BY FECHA_DEVOLUCION DESC;", textBox1.Text.Trim());
cmd = new OdbcCommand(sql, conexion);
reader = cmd.ExecuteReader();
if (reader.Read())
{
dataGridView1.Rows.Add(reader.GetString(0), reader.GetString(1), reader.GetString(2) + " " + reader.GetString(3), reader.GetString(4), reader.GetString(5), string.Format("Q.{0:###,###,###,##0.00##}", reader.GetDouble(6)), string.Format("Q.{0:###,###,###,##0.00##}", reader.GetDouble(7)), string.Format("Q.{0:###,###,###,##0.00##}", reader.GetDouble(8)), reader.GetString(9), reader.GetString(10), reader.GetString(11));
while (reader.Read())
{
dataGridView1.Rows.Add(reader.GetString(0), reader.GetString(1), reader.GetString(2) + " " + reader.GetString(3), reader.GetString(4), reader.GetString(5), string.Format("Q.{0:###,###,###,##0.00##}", reader.GetDouble(6)), string.Format("Q.{0:###,###,###,##0.00##}", reader.GetDouble(7)), string.Format("Q.{0:###,###,###,##0.00##}", reader.GetDouble(8)), reader.GetString(9), reader.GetString(10), reader.GetString(11));
}
}
else
{
dataGridView1.Rows.Clear();
sql = string.Format("SELECT * FROM VISTA_DEVOLUCIONES WHERE SUBTOTAL_FACTURA LIKE '%{0}%' ORDER BY FECHA_DEVOLUCION DESC;", textBox1.Text.Trim());
cmd = new OdbcCommand(sql, conexion);
reader = cmd.ExecuteReader();
if (reader.Read())
{
dataGridView1.Rows.Add(reader.GetString(0), reader.GetString(1), reader.GetString(2) + " " + reader.GetString(3), reader.GetString(4), reader.GetString(5), string.Format("Q.{0:###,###,###,##0.00##}", reader.GetDouble(6)), string.Format("Q.{0:###,###,###,##0.00##}", reader.GetDouble(7)), string.Format("Q.{0:###,###,###,##0.00##}", reader.GetDouble(8)), reader.GetString(9), reader.GetString(10), reader.GetString(11));
while (reader.Read())
{
dataGridView1.Rows.Add(reader.GetString(0), reader.GetString(1), reader.GetString(2) + " " + reader.GetString(3), reader.GetString(4), reader.GetString(5), string.Format("Q.{0:###,###,###,##0.00##}", reader.GetDouble(6)), string.Format("Q.{0:###,###,###,##0.00##}", reader.GetDouble(7)), string.Format("Q.{0:###,###,###,##0.00##}", reader.GetDouble(8)), reader.GetString(9), reader.GetString(10), reader.GetString(11));
}
}
else
{
dataGridView1.Rows.Clear();
sql = string.Format("SELECT * FROM VISTA_DEVOLUCIONES WHERE DESCUENTO_FACTURA LIKE '%{0}%' ORDER BY FECHA_DEVOLUCION DESC;", textBox1.Text.Trim());
cmd = new OdbcCommand(sql, conexion);
reader = cmd.ExecuteReader();
if (reader.Read())
{
dataGridView1.Rows.Add(reader.GetString(0), reader.GetString(1), reader.GetString(2) + " " + reader.GetString(3), reader.GetString(4), reader.GetString(5), string.Format("Q.{0:###,###,###,##0.00##}", reader.GetDouble(6)), string.Format("Q.{0:###,###,###,##0.00##}", reader.GetDouble(7)), string.Format("Q.{0:###,###,###,##0.00##}", reader.GetDouble(8)), reader.GetString(9), reader.GetString(10), reader.GetString(11));
while (reader.Read())
{
dataGridView1.Rows.Add(reader.GetString(0), reader.GetString(1), reader.GetString(2) + " " + reader.GetString(3), reader.GetString(4), reader.GetString(5), string.Format("Q.{0:###,###,###,##0.00##}", reader.GetDouble(6)), string.Format("Q.{0:###,###,###,##0.00##}", reader.GetDouble(7)), string.Format("Q.{0:###,###,###,##0.00##}", reader.GetDouble(8)), reader.GetString(9), reader.GetString(10), reader.GetString(11));
}
}
else
{
dataGridView1.Rows.Clear();
sql = string.Format("SELECT * FROM VISTA_DEVOLUCIONES WHERE TOTAL_FACTURA LIKE '%{0}%' ORDER BY FECHA_DEVOLUCION DESC;", textBox1.Text.Trim());
cmd = new OdbcCommand(sql, conexion);
reader = cmd.ExecuteReader();
if (reader.Read())
{
dataGridView1.Rows.Add(reader.GetString(0), reader.GetString(1), reader.GetString(2) + " " + reader.GetString(3), reader.GetString(4), reader.GetString(5), string.Format("Q.{0:###,###,###,##0.00##}", reader.GetDouble(6)), string.Format("Q.{0:###,###,###,##0.00##}", reader.GetDouble(7)), string.Format("Q.{0:###,###,###,##0.00##}", reader.GetDouble(8)), reader.GetString(9), reader.GetString(10), reader.GetString(11));
while (reader.Read())
{
dataGridView1.Rows.Add(reader.GetString(0), reader.GetString(1), reader.GetString(2) + " " + reader.GetString(3), reader.GetString(4), reader.GetString(5), string.Format("Q.{0:###,###,###,##0.00##}", reader.GetDouble(6)), string.Format("Q.{0:###,###,###,##0.00##}", reader.GetDouble(7)), string.Format("Q.{0:###,###,###,##0.00##}", reader.GetDouble(8)), reader.GetString(9), reader.GetString(10), reader.GetString(11));
}
}
else
{
dataGridView1.Rows.Clear();
sql = string.Format("SELECT * FROM VISTA_DEVOLUCIONES WHERE VENDEDOR LIKE '%{0}%' ORDER BY FECHA_DEVOLUCION DESC;", textBox1.Text.Trim());
cmd = new OdbcCommand(sql, conexion);
reader = cmd.ExecuteReader();
if (reader.Read())
{
dataGridView1.Rows.Add(reader.GetString(0), reader.GetString(1), reader.GetString(2) + " " + reader.GetString(3), reader.GetString(4), reader.GetString(5), string.Format("Q.{0:###,###,###,##0.00##}", reader.GetDouble(6)), string.Format("Q.{0:###,###,###,##0.00##}", reader.GetDouble(7)), string.Format("Q.{0:###,###,###,##0.00##}", reader.GetDouble(8)), reader.GetString(9), reader.GetString(10), reader.GetString(11));
while (reader.Read())
{
dataGridView1.Rows.Add(reader.GetString(0), reader.GetString(1), reader.GetString(2) + " " + reader.GetString(3), reader.GetString(4), reader.GetString(5), string.Format("Q.{0:###,###,###,##0.00##}", reader.GetDouble(6)), string.Format("Q.{0:###,###,###,##0.00##}", reader.GetDouble(7)), string.Format("Q.{0:###,###,###,##0.00##}", reader.GetDouble(8)), reader.GetString(9), reader.GetString(10), reader.GetString(11));
}
}
}
}
}
}
}
}
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
conexion.Close();
}
else
{
cargaDatos();
}
}
private void check_but_Click(object sender, RoutedEventArgs e)
{
if (!string.IsNullOrEmpty(user_tBox.Text) && !string.IsNullOrEmpty(upword_tBox.Password) && !string.IsNullOrEmpty(comname_tBox.Text))
{
try
{
if (Connection.Connect(ConfigurationManager.AppSettings["sip"], ConfigurationManager.AppSettings["susername"], Util.DecryptDES(ConfigurationManager.AppSettings["spassword"]), ConfigurationManager.AppSettings["sport"], ConfigurationManager.AppSettings["dbname"]) == true)//connect with server with new update feild
{
//connect main database and get details which include company information
using (OdbcCommand MyCommand = new OdbcCommand("SELECT db_name,db_username,db_password,db_host,db_port from tms_connections where company_name='" + comname_tBox.Text + "'", Connection.MyConnection))
{
OdbcDataReader MyDataReader = MyCommand.ExecuteReader();
MyDataReader.Read();
companyDBName = MyDataReader.GetString(0);
companyDBsusername = MyDataReader.GetString(1);
companyDBspassword = MyDataReader.GetString(2);
companyDBsip = MyDataReader.GetString(3);
companyDBsport = MyDataReader.GetString(4);
}
}
Connection.MyConnection.Close();
//connect company database
if ((companyDBName != null) && (companyDBsusername != null) && (companyDBsip != null) && (companyDBsport != null))
{
if (Connection.MyConnection.State == ConnectionState.Open)
{
Connection.MyConnection.Close();
}
if (Connection.Connect(companyDBsip, companyDBsusername, companyDBspassword, companyDBsport, companyDBName) == true)//connect with server with new update feild
{
//console.writeline("companyDBsip " + companyDBsip);
//console.writeline("companyDBsusername " + companyDBsusername);
//console.writeline("companyDBspassword " + companyDBspassword);
//console.writeline("companyDBsport " + companyDBsport);
//console.writeline("companyDBName " + companyDBName);
Login lg = new Login();
if (lg.checkuser(user_tBox.Text, upword_tBox.Password) == true)//check username and password weather correct or incorrect
{
if (Microsoft.Windows.Controls.MessageBox.Show("Do you want to update application settings?", "BQuTMSWithJira Update Settings", MessageBoxButton.YesNo, MessageBoxImage.Question) == MessageBoxResult.Yes)
{
Mouse.OverrideCursor = Cursors.Wait;
UpdateConfigFile();
downloadUserImage();
Assembly assem = Assembly.GetEntryAssembly();
AssemblyName assemName = assem.GetName();
lg.UpdateVersion(assemName.Version.ToString(), user_tBox.Text);
Connection.MyConnection.Close();
var info = new ProcessStartInfo();
info.FileName = "ProcessReStarter";
info.WindowStyle = ProcessWindowStyle.Hidden;
Process.Start(info);
Environment.Exit(0);
}
}
}
}
else
{
Mouse.OverrideCursor = null;
Microsoft.Windows.Controls.MessageBox.Show("Please check company name again.", "BQuTMSWithJira Company Name Incorrect", MessageBoxButton.OK, MessageBoxImage.Hand);
}
}
catch (Exception)
{
Mouse.OverrideCursor = null;
Microsoft.Windows.Controls.MessageBox.Show("Please check company name again.", "BQuTMSWithJira Company Name Incorrect", MessageBoxButton.OK, MessageBoxImage.Hand);
}
}
else
{
Microsoft.Windows.Controls.MessageBox.Show("Please fill the field(s).", "BQuTMSWithJira Empty Field", MessageBoxButton.OK, MessageBoxImage.Warning);
}
}
/// <summary>
///
/// select a row from table t_RBSR_AUFW_u_BusRoleOwner.
/// </summary>
/// <param name="ID"></param>
/// <returns>returnGetBusRoleOwner</returns>
public returnGetBusRoleOwner GetBusRoleOwner(int ID)
{
returnGetBusRoleOwner rv = new returnGetBusRoleOwner();
DBConnect();
OdbcCommand cmd = _dbConnection.CreateCommand();
cmd.CommandText = "select \"c_id\",\"c_u_EID\",\"c_u_Geography\",\"c_u_Rank\",\"c_r_BusRole\", (SELECT ForDisplay FROM DICT_RoleOwnerType where Abbrev=c_u_Rank) as Friendly from \"t_RBSR_AUFW_u_BusRoleOwner\" where \"c_id\"= ?";
cmd.Parameters.Add("c_id", OdbcType.Int);
cmd.Parameters["c_id"].Value = (object)ID;
cmd.Connection = _dbConnection;
OdbcDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
if (dr.IsDBNull(0))
{
cmd.Dispose(); DBClose(); throw new Exception("Value 'null' is not allowed for 'ID'");
}
else
{
rv.ID = dr.GetInt32(0);
}
if (dr.IsDBNull(1))
{
cmd.Dispose(); DBClose(); throw new Exception("Value 'null' is not allowed for 'EID'");
}
else
{
rv.EID = dr.GetString(1);
}
if (dr.IsDBNull(2))
{
cmd.Dispose(); DBClose(); throw new Exception("Value 'null' is not allowed for 'Geography'");
}
else
{
rv.Geography = dr.GetString(2);
}
if (dr.IsDBNull(3))
{
cmd.Dispose(); DBClose(); throw new Exception("Value 'null' is not allowed for 'Rank'");
}
else
{
rv.Rank = dr.GetString(3);
}
if (dr.IsDBNull(4))
{
cmd.Dispose(); DBClose(); throw new Exception("Value 'null' is not allowed for 'BusRoleID'");
}
else
{
rv.BusRoleID = dr.GetInt32(4);
}
rv.RankFriendly = dr.GetString(5);
}
dr.Close();
dr.Dispose();
cmd.Dispose();
DBClose();
return(rv);
}