public static void getAllProducts()
{
if (prodAdapter == null)
{
setupProdAdapter();
}
prodCmd.CommandText = "Select * from product order by ProductID";
try
{
if (!(tblproduct == null))
{
tblproduct.Clear();
}
prodAdapter.Fill(tblproduct);
}
catch (Exception e)
{
throw e;
}
finally
{
con.Close();
}
}
private void bt_recherch_Click(object sender, EventArgs e)
{
clearColor(); table.Clear();
cb_envoi_dfi_paiement.Items.Clear();
cb_envoi_dfi_paiement.Items.Add("NON");
cb_envoi_dfi_paiement.Items.Add("OUI");
cb_fournisseur.Text = cb_fournisseur.SelectedText.ToString();
if (tb_nFacture.Text == "" && cb_fournisseur.Text == "")
{
MessageBox.Show("le champ fournisseur ou le champ n facture est vide", "error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else
{
//***************************************************************************************
List <CpoBox> lpb = new List <CpoBox>(); //list de pobox
lpb = cc.get_pobox_by_nfact_or_fourniss(tb_nFacture.Text, cb_fournisseur.Text); //call function that returns list of pobox from the database
int Lsize = lpb.Count(); //taille de list retourner
table.Clear(); //clear table from data
List <int> l = new List <int>(); //to store results of the dfi if oui then 1 else 0
//MessageBox.Show(Convert.ToString(lpb.Count()));
//string bb = "";
for (int i = 0; i < Lsize; i += 1)
{//adding all elements in list to table
table.Rows.Add("", lpb[i].date_reception, lpb[i].direction, lpb[i].emetteur, lpb[i].destinataire, lpb[i].fournisseur, lpb[i].date_facture, lpb[i].montant, lpb[i].devise, lpb[i].objet_facture, lpb[i].Nfacture, lpb[i].Ncommande, lpb[i].date_envoi_fedosa, lpb[i].restitution_fedosa, lpb[i].envoi_dfi, lpb[i].remarque, lpb[i].pole, lpb[i].date_envoi_dfi);
//bb += lpb[i].direction + "#";
if (lpb[i].envoi_dfi == "OUI")
{
l.Add(1);
}
else
{
l.Add(0);
}
//dataGridView1.DataSource = table;
}
// MessageBox.Show(bb);
dataGridView1.DataSource = table;
for (int i = 0; i < Lsize; i += 1)//scroll to datagrid and test if 1 or 0 if 1 then fill in green (1 means resultat dfi=oui)
{
if (l[i] == 1)
{
dataGridView1.Rows[i].Cells[0].Style.BackColor = Color.Green;//set column to green if envoie dfi est oui
}
}
dataGridView1.Columns[0].Width = 30;//resize of acc dfi column
ddatagridWidth();
}
}
//=======================================================
//Глобальные процедуры и функции ========================
public void TreeInitialize()
{
//Подключение локальной базы данных (список серверов)
try{
OleDb_Connection.ConnectionString = ClassConfig.ConnectLineBegin + ClassConfig.Rapid_FileListBase + ClassConfig.ConnectLineEnd + ClassConfig.ConnectPass;
OleDb_Connection.Open(); //соединение с базой
//Создание таблицы DataTable
DataTable_ListBase.Clear();
DataTable_ListBase.CaseSensitive = false;
DataTable_ListBase.Columns.Add("Name", Type.GetType("System.String"));
DataTable_ListBase.Columns.Add("Server", Type.GetType("System.String"));
DataTable_ListBase.Columns.Add("DataBase", Type.GetType("System.String"));
DataTable_ListBase.Columns.Add("Uid", Type.GetType("System.String"));
DataTable_ListBase.Columns.Add("Pwd", Type.GetType("System.String"));
DataTable_ListBase.Columns.Add("Admin", Type.GetType("System.String"));
DataTable_ListBase.Columns.Add("Client", Type.GetType("System.String"));
//Вставка таблицы в DataSet
DataSet_ListBase.Clear();
DataSet_ListBase.Tables.Add(DataTable_ListBase);
DataSet_ListBase.DataSetName = "ListBase";
OleDb_CommandSelect = new OleDbCommand("SELECT * FROM ListBase", OleDb_Connection);
OleDb_CommandDelete = new OleDbCommand("DELETE FROM ListBase WHERE ([ID_ListBase] = ?) AND (Name = ? OR ? IS NULL AND Name IS NULL) AND (Server = ? OR ? IS NULL AND Server IS NULL) AND (DataBase = ? OR ? IS NULL AND DataBase IS NULL) AND (Uid = ? OR ? IS NULL AND Uid IS NULL) AND (Pwd = ? OR ? IS NULL AND Pwd IS NULL) AND (Admin = ? OR ? IS NULL AND Admin IS NULL) AND (Client = ? OR ? IS NULL AND Client IS NULL)", OleDb_Connection);
OleDb_CommandUpdate = new OleDbCommand("UPDATE ListBase SET [Name] = ?, [Server] = ?, [DataBase] = ?, [Uid] = ?, [Pwd] = ?, [Admin] = ?, [Client] = ? WHERE ([ID_ListBase] = ?) AND (Name = ? OR ? IS NULL AND Name IS NULL) AND (Server = ? OR ? IS NULL AND Server IS NULL) AND (DataBase = ? OR ? IS NULL AND DataBase IS NULL) AND (Uid = ? OR ? IS NULL AND Uid IS NULL) AND (Pwd = ? OR ? IS NULL AND Pwd IS NULL) AND (Admin = ? OR ? IS NULL AND Admin IS NULL) AND (Client = ? OR ? IS NULL AND Client IS NULL)", OleDb_Connection);
OleDb_CommandInsert = new OleDbCommand("INSERT INTO ListBase ([Name], [Server], [DataBase], [Uid], [Pwd], [Admin], [Client]) VALUES (?, ?, ?, ?, ?, ?, ?)", OleDb_Connection);
OleDb_DataAdapter1.SelectCommand = OleDb_CommandSelect;
OleDb_DataAdapter1.DeleteCommand = OleDb_CommandDelete;
OleDb_DataAdapter1.UpdateCommand = OleDb_CommandUpdate;
OleDb_DataAdapter1.InsertCommand = OleDb_CommandInsert;
OleDb_DataAdapter1.Fill(DataSet_ListBase, "ListBase");
OleDb_Connection.Close(); //отключение соединения
// Выгрузка данных
treeView1.Nodes.Clear();
treeView1.Nodes.Add("Конфигурации").ImageKey = "folder_close.png";
treeView1.Nodes[0].SelectedImageKey = "folder_close.png";
foreach (System.Data.DataTable table in DataSet_ListBase.Tables)
{
foreach (System.Data.DataRow row in table.Rows)
{
treeView1.Nodes[0].Nodes.Add(row[1].ToString()).Tag = row[0].ToString();
}
}
}catch (Exception ex) {
MessageBox.Show(ex.ToString()); //Сообщение об ошибке
OleDb_Connection.Close();
Application.Exit();
}
}
public static void getUserData()
{
if (userAdapter == null)
{
setupUserDataAdapter();
}
userCmd.CommandText = "Select * from UserData order by userID";
try
{
if (!(tblUserData == null))
{
tblUserData.Clear();
}
userAdapter.Fill(tblUserData);
}
catch (Exception e)
{
throw e;
}
finally
{
con.Close();
}
}
public static System.Data.DataTable GetPagedTable(this System.Data.DataTable dt, int PageIndex, int PageSize)//PageIndex表示第几页，PageSize表示每页的记录数
{
if (PageIndex == 0)
{
return(dt);//0页代表每页数据，直接返回
}
System.Data.DataTable newdt = dt.Copy();
newdt.Clear();//copy dt的框架
int rowbegin = (PageIndex - 1) * PageSize;
int rowend = PageIndex * PageSize;
if (rowbegin >= dt.Rows.Count)
{
return(newdt);//源数据记录数小于等于要显示的记录，直接返回dt
}
if (rowend > dt.Rows.Count)
{
rowend = dt.Rows.Count;
}
for (int i = rowbegin; i <= rowend - 1; i++)
{
System.Data.DataRow newdr = newdt.NewRow();
System.Data.DataRow dr = dt.Rows[i];
foreach (System.Data.DataColumn column in dt.Columns)
{
newdr[column.ColumnName] = dr[column.ColumnName];
}
newdt.Rows.Add(newdr);
}
return(newdt);
}
public static void getAllUsers()
{
if (prodAdapter == null)
{
setupProdAdapter();
}
prodCmd.CommandText = "Select * from user order by Id";
try
{
if (!(user == null))
{
user.Clear();
}
prodAdapter.Fill(user);
}
catch (Exception e)
{
throw e;
}
finally
{
con.Close();
}
}
public Boolean Get_Job_Details(Int32 jobId)
{
Boolean isSuccessful = true;
ErrorMessage = string.Empty;
try
{
String strSQL = "SELECT * FROM JobDetails ";
strSQL = strSQL + "INNER JOIN WorkOrderDetails ON JobDetails.WorkOrderDetailId = WorkOrderDetails.WorkorderDetailId ";
strSQL = strSQL + "INNER JOIN Parts ON WorkOrderDetails.PartId = Parts.PartId ";
strSQL = strSQL + "WHERE JobDetails.Jobid = " + jobId.ToString();
System.Data.SqlClient.SqlCommand cmdGet = new System.Data.SqlClient.SqlCommand(strSQL, myVPSConnection);
System.Data.SqlClient.SqlDataReader rdrGet = cmdGet.ExecuteReader();
if (rdrGet.HasRows == true)
{
myJobDetails.Clear();
myJobDetails.Load(rdrGet);
}
rdrGet.Close();
cmdGet.Dispose();
}
catch (Exception ex)
{
isSuccessful = false;
ErrorMessage = "Get Job Details - " + ex.Message;
}
return(isSuccessful);
}
private void CreateDbfTable()
{
if (_dbfTable != null)
{
_dbfTable.Clear();
_dbfTable = null;
}
_dbfTable = new System.Data.DataTable();
_dbfTable.TableName = this.TableName;
for (int k = 0; k < this._fieldCount; k++)
{
System.Data.DataColumn col = new System.Data.DataColumn();
string colText = this._dbfFields[k].GetFieldName(_encoding);
if (string.IsNullOrEmpty(colText) == true)
{
throw new Exception("the " + (k + 1) + "th column name is null.");
}
col.ColumnName = colText;
col.Caption = colText;
col.DataType = this._dbfFields[k].FieldType;
_dbfTable.Columns.Add(col);
}
}
public static void getAllCart()
{
if (ProdAdapter == null)
{
setupProdAdapterCart();
}
prodCmd.CommandText = "Select * from Cart order by CartProdID";
try
{
if (!(tblProduct == null))
{
tblCart.Clear();
}
ProdAdapter.Fill(tblCart);
}
catch (Exception e)
{
throw e;
}
finally
{
con.Close();
}
}
public static void getRoleData()
{
if (roleAdapter == null)
{
setupRoleAdapter();
}
roleCmd.CommandText = "Select * from RoleInfo order by RoleName";
try
{
if (!(tblRoleData == null))
{
tblRoleData.Clear();
}
roleAdapter.Fill(tblRoleData);
}
catch (Exception e)
{
throw e;
}
finally
{
con.Close();
}
}
} // End Sub ExportDatabase
public override void ImportTableData()
{
string strBasePath = @"D:\Stefan.Steiger\Desktop\ValidationResults\SRGSSRReis_Export_sts";
System.Data.DataTable dtTableInOrder = new System.Data.DataTable();
dtTableInOrder.ReadXml(strBasePath = System.IO.Path.Combine(System.IO.Path.Combine(strBasePath, "DependencyOrder"), "Foreign_Key_Depencency_Order.xml"));
foreach (System.Data.DataRow dr in dtTableInOrder.Rows)
{
string strDestTable = System.Convert.ToString(dr["TableName"]);
System.Console.WriteLine(strDestTable);
string strPath = System.IO.Path.Combine(strBasePath, "Tables");
strPath = System.IO.Path.Combine(strPath, strDestTable + ".xml");
System.Console.WriteLine(strPath);
System.Data.DataTable dt = new System.Data.DataTable();
dt.ReadXml(strPath);
BulkCopy(strDestTable, dt);
dt.Clear();
dt.Dispose();
} // Next dr
dtTableInOrder.Clear();
dtTableInOrder.Dispose();
} // End Sub ImportTableData
}//Code93
/// <summary>
/// Encode the raw data using the Code 93 algorithm.
/// </summary>
private string Encode_Code93()
{
init_Code93();
var formattedData = Add_CheckDigits(Raw_Data);
var result = C93_Code.Select("Character = '*'")[0]["Encoding"].ToString();
foreach (var c in formattedData)
{
try
{
result += C93_Code.Select("Character = '" + c + "'")[0]["Encoding"].ToString();
}//try
catch
{
Error("EC93-1: Invalid data.");
} //catch
} //foreach
result += C93_Code.Select("Character = '*'")[0]["Encoding"].ToString();
//termination bar
result += "1";
//clear the hashtable so it no longer takes up memory
C93_Code.Clear();
return(result);
}//Encode_Code93
public Boolean Get_Utilisation_Record(Int32 lineId, DateTime workDay)
{
Boolean isSuccessful = true;
ErrorMessage = string.Empty;
try
{
String strSQL = "SELECT * FROM ProductionLineUtilisation WHERE LineUtilisationLineId = " + lineId.ToString() + " AND LineUtilisationWorkDate = CONVERT(datetime,'" + workDay.ToShortDateString() + " 00:00:00', 103)";
System.Data.SqlClient.SqlCommand cmdGet = new System.Data.SqlClient.SqlCommand(strSQL, myVPSConnection);
System.Data.SqlClient.SqlDataReader rdrGet = cmdGet.ExecuteReader();
if (rdrGet.HasRows == true)
{
UtilisationRecord.Clear();
UtilisationRecord.Load(rdrGet);
isSuccessful = Gather_Utilisation_Record();
}
else
{
isSuccessful = false;
ErrorMessage = "Get Production Line Utilisation Record - Not Found !";
}
rdrGet.Close();
cmdGet.Dispose();
}
catch (Exception ex)
{
isSuccessful = false;
ErrorMessage = "Get Production Line Utilisation Record - " + ex.Message;
}
return(isSuccessful);
}
public static void getAllCheckout()
{
if (prodAdapter == null)
{
setupProdAdapter();
}
prodCmd.CommandText = "Select * from checkout order by productId";
try
{
if (!(user == null))
{
checkout.Clear();
}
prodAdapter.Fill(checkout);
}
catch (Exception e)
{
throw e;
}
finally
{
con.Close();
}
}
public static void getShoppingCartData()
{
if (cartAdapter == null)
{
setupShoppingCartAdapter();
}
cartCmd.CommandText = "Select * from ShoppingCart order by ProductID";
try
{
if (!(tblShoppingCart == null))
{
tblShoppingCart.Clear();
}
cartAdapter.Fill(tblShoppingCart);
}
catch (Exception e)
{
throw e;
}
finally
{
con.Close();
}
}
public static void getAllLogin()
{
if (ProdAdapter == null)
{
setupProdAdapter();
}
prodCmd.CommandText = "Select * from Login order by UserName";
try
{
if (!(tblProduct == null))
{
tblLogin.Clear();
}
ProdAdapter.Fill(tblLogin);
}
catch (Exception e)
{
throw e;
}
finally
{
con.Close();
}
}
public void SNO_IN(string date_1, string date_2, System.Data.DataTable dt)
{
var GR = new General_Reestr();
string path = AppDomain.CurrentDomain.BaseDirectory + @"ReportTemplates\СНО Приход.xlsx";
using (SLDocument sl = new SLDocument(path))
{
sl.SelectWorksheet("СНО Приход");
sl.SetCellValue("B1", "Приход СНО в ТОО \"Batys Petroleum\" за период с " + date_1 + " по " + date_2);
for (int i = 0; i < dt.Rows.Count; i++)
{
for (int j = 1; j < dt.Columns.Count; j++)
{
if (j == 1)
{
sl.SetCellValue(i + 4, j, Convert.ToDecimal(dt.Rows[i][j].ToString()));
sl.SetCellStyle(i + 4, j, GR.FormattingExcelCells(sl, true));
}
else
{
sl.SetCellStyle(i + 4, j + 1, GR.FormattingExcelCells(sl, true));
if (j > 1 && j < 4)
{
sl.SetCellValue(i + 4, j + 1, Convert.ToDecimal(dt.Rows[i][j].ToString()));
}
else
{
sl.SetCellValue(i + 4, j + 1, dt.Rows[i][j].ToString());
}
}
}
sl.SetCellValue(i + 4, 2, $"=C{i + 4} + D{i + 4}");
sl.SetCellStyle(i + 4, 2, GR.FormattingExcelCells(sl, true));
//backgroundWorker1.ReportProgress(i);
}
for (int j = 0; j < dt.Columns.Count; j++)
{
sl.SetCellStyle(dt.Rows.Count + 4, j + 1, GR.FormattingExcelCells(sl, true));
}
sl.SetCellValue(dt.Rows.Count + 4, 1, "Итог");
sl.SetCellValue(dt.Rows.Count + 4, 2, $"=SUM(B{4}:C{dt.Rows.Count + 3})");
sl.SetCellValue(dt.Rows.Count + 4, 3, $"=SUM(C{4}:C{dt.Rows.Count + 3})");
sl.SetCellValue(dt.Rows.Count + 4, 4, $"=SUM(D{4}:D{dt.Rows.Count + 3})");
sl.SaveAs(AppDomain.CurrentDomain.BaseDirectory + @"Report\СНО Приход.xlsx");
}
dt.Clear();
Process.Start(AppDomain.CurrentDomain.BaseDirectory + @"Report\СНО Приход.xlsx");
}
/// <summary>
/// Datat table qui recupere la collection de contrats
/// </summary>
/// <returns></returns>
public System.Data.DataTable ListerContrats()
{
dtContrats.Clear();
foreach (MContrat unContrat in this.Contrats.Values)
{
this.dtContrats.Rows.Add(unContrat.NumeroContrat, unContrat.Qualification, unContrat.Statut, unContrat.DateDebut);
}
return(this.dtContrats);
}
public async override Task RunCommand(object sender)
{
var engine = (IAutomationEngineInstance)sender;
OBDataTable dataTable = (OBDataTable)await v_DataTable.EvaluateCode(engine);
dataTable.Clear();
dataTable.SetVariableValue(engine, v_OutputUserVariableName);
}
public override void RunCommand(object sender)
{
var engine = (IAutomationEngineInstance)sender;
var dataTableVariable = v_DataTable.ConvertUserVariableToObject(engine, nameof(v_DataTable), this);
OBDataTable dataTable = (OBDataTable)dataTableVariable;
dataTable.Clear();
dataTable.StoreInUserVariable(engine, v_DataTable, nameof(v_DataTable), this);
}
bool[] multiInOper = { false, true, false, true, false, false, false, false, false, false, true, false, true, true, false }; //флаг поддержки оператором входов более 2-х
//метод загрузки словаря из БД
public void LoadDictionary(string BaseAddr)
{
List <WordsOfDictionary> DictionaryList = new List <WordsOfDictionary>();
DictionaryList.Add(Marka);
DictionaryList.Add(TypeFB);
DictionaryList.Add(TypeFunc);
DictionaryList.Add(TIOCH);
ConnectToDB connect = new ConnectToDB();
//в цикле для каждого словаря делаем запрос и заполняем его
foreach (WordsOfDictionary Dictionary in DictionaryList)
{
connect.ConnectToBase(Dictionary.SQL, BaseAddr);
TmpDG = connect.dt1;
for (int i = 0; i < TmpDG.Rows.Count; i++)
{
Dictionary.ResultList.Add(TmpDG.Rows[i][0].ToString());
}
TmpDG.Clear();
}
//добавление словаря операторов почти вручную
for (int i = 0; i < nameOper.Length; i++)
{
Operators op = new Operators();
op.OperName = nameOper[i];
op.OperLevel = levelOper[i];
op.OperID = i;
op.OperMultiIn = multiInOper[i];
Oper.Add(op);
}
/*
* Oper.ResultList.Add("-");
* Oper.ResultList.Add("*");
* Oper.ResultList.Add("/");
* Oper.ResultList.Add("+");
* Oper.ResultList.Add("<"); // в xml: <
* Oper.ResultList.Add("<="); // в xml: <=
* Oper.ResultList.Add("<>"); // в xml: <>
* Oper.ResultList.Add("=");
* Oper.ResultList.Add(">"); // в xml: >
* Oper.ResultList.Add(">="); // в xml: >=
* Oper.ResultList.Add("AND");
* Oper.ResultList.Add("NOT");
* Oper.ResultList.Add("OR");
* Oper.ResultList.Add("XOR");
* Oper.ResultList.Add("1 gain");
*
*/
}
/// <summary>
/// 反序列化 DataTable 成 实体类数据集合
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="dt"></param>
/// <param name="ifClearDataTable">是否清空 数据源 集合 释放资源</param>
/// <returns></returns>
public static List <T> DeserializeFromDataTable <T>(System.Data.DataTable dt, bool ifClearDataTable = false) where T : class
{
var list = JsonSerializeHelper.DeserializeFromJson <List <T> >(JsonSerializeHelper.SerializeToJson(dt));
if (ifClearDataTable)
{
dt?.Clear();
}
return(list);
}
/// <summary>
/// 清空内存表中的数据记录。
/// </summary>
private void Clear()
{
if (null != mPointTable)
{
mPointTable.Clear();
}
if (null != mLineTable)
{
mLineTable.Clear();
}
if (null != mTextTable)
{
mTextTable.Clear();
}
}
} // End Function IsTableEmpty
public override bool TableHasColumn(string strTableName, string strColumnName)
{
System.Data.DataTable dt = GetColumnNamesForTable(strTableName);
int i = dt.Select("ColumnName like '" + strColumnName + "'").Length;
dt.Clear();
dt.Dispose();
if (i > 0)
{
return(true);
}
return(false);
} // End Function TableHasColumn
public static void ReadFromXml(this System.Data.DataTable aDataTable, XmlNode aXmlNode)
{
if (aDataTable == null)
{
return;
}
aDataTable.Clear();
if (aXmlNode == null)
{
return;
}
System.IO.StringReader aStringReader = new System.IO.StringReader(aXmlNode.InnerText);
XmlReader aXmlReader = XmlReader.Create(aStringReader);
aDataTable.ReadXml(aXmlReader);
}
public void EjecutaConsulta(string SQL, System.Data.DataTable dt)
{
// Se especifica la instrucción que se lanzará al servidor
com = new MySqlCommand(SQL, con);
MySqlDataAdapter adaptador = new MySqlDataAdapter();
adaptador.SelectCommand = com;
dt.Clear();
try
{
adaptador.Fill(dt);
}
catch (Exception error)
{
throw error;
}
}
public IActionResult Get()
{
string mimtype = "";
int extension = 1;
var _reportPath = @"Reports\testeReport.rdlc";
LocalReport localReport = new LocalReport(_reportPath);
//Dados
System.Data.DataTable dt = new System.Data.DataTable();
dt.Clear();
dt.Columns.Add("Id", typeof(int));
dt.Columns.Add("Name", typeof(string));
dt.Rows.Add(1, "Paulo");
dt.Rows.Add(2, "Jose");
localReport.AddDataSource("DataSet1", dt);
//Parametros do relatório
var reportParams = new Dictionary <string, string>();
//reportParams.Add("Key1", "value1");
//reportParams.Add("Key2", "value2");
if (reportParams != null && reportParams.Count > 0)// if you use parameter in report
{
List <ReportParameter> reportparameter = new List <ReportParameter>();
foreach (var record in reportParams)
{
reportparameter.Add(new ReportParameter());
}
}
//Geração do arquivo
System.Text.Encoding.RegisterProvider(System.Text.CodePagesEncodingProvider.Instance);
var result = localReport.Execute(RenderType.Pdf, extension, parameters: reportParams);
byte[] file = result.MainStream;
Stream stream = new MemoryStream(file);
return(File(stream, "application/pdf", "testeReport.pdf"));
}
void loadData()
{
//Подключение локальной базы данных (список серверов)
try{
oleDbConnection.ConnectionString = DataConfig.oledbConnectLineBegin + DataConfig.configFile + DataConfig.oledbConnectLineEnd + DataConfig.oledbConnectPass;
oleDbConnection.Open(); //соединение с базой
//Создание таблицы DataTable
oleDbDataTable.Clear();
oleDbDataTable.CaseSensitive = false;
oleDbDataTable.Columns.Add("Name", Type.GetType("System.String"));
oleDbDataTable.Columns.Add("Pass", Type.GetType("System.String"));
oleDbDataTable.Columns.Add("Permissions", Type.GetType("System.String"));
//Вставка таблицы в DataSet
oleDbDataSet.Clear();
oleDbDataSet.Tables.Add(oleDbDataTable);
oleDbDataSet.DataSetName = "ListUsers";
oleDbCommandSelect = new OleDbCommand("SELECT * FROM Users", oleDbConnection);
oleDbCommandDelete = new OleDbCommand("DELETE FROM Users WHERE ([ID] = ?) " +
"AND (Name = ? OR ? IS NULL AND Name IS NULL) " +
"AND (Pass = ? OR ? IS NULL AND Pass IS NULL) " +
"AND (Permissions = ? OR ? IS NULL AND Permissions IS NULL)", oleDbConnection);
oleDbCommandUpdate = new OleDbCommand("UPDATE Users SET [Name] = ?, [Pass] = ?, [Permissions] = ? WHERE ([ID] = ?) " +
"AND (Name = ? OR ? IS NULL AND Name IS NULL) " +
"AND (Pass = ? OR ? IS NULL AND Pass IS NULL) " +
"AND (Permissions = ? OR ? IS NULL AND Permissions IS NULL)", oleDbConnection);
oleDbCommandInsert = new OleDbCommand("INSERT INTO Users ([Name], [Pass], [Permissions]) VALUES (?, ?, ?)", oleDbConnection);
oleDbDataAdapter1.SelectCommand = oleDbCommandSelect;
oleDbDataAdapter1.DeleteCommand = oleDbCommandDelete;
oleDbDataAdapter1.UpdateCommand = oleDbCommandUpdate;
oleDbDataAdapter1.InsertCommand = oleDbCommandInsert;
oleDbDataAdapter1.Fill(oleDbDataSet, "Users");
oleDbConnection.Close(); //отключение соединения
}catch (Exception ex) {
MessageBox.Show(ex.ToString());
oleDbConnection.Close();
Application.Exit();
}
readData();
}
// Multi Coat
public Int32 Get_MultiCoat_Progress_Records(Int32 jobId)
{
Int32 recordCount = 0;
System.Data.DataTable myMCProgress = new System.Data.DataTable();
ErrorMessage = string.Empty;
try
{
myMCProgress.Clear();
String strSQL = "SELECT JobProgress.*, ";
strSQL = strSQL + "Jobs.*, ";
strSQL = strSQL + "WorkOrders.WorkOrderNo, WorkOrders.CustomerRef, ";
strSQL = strSQL + "PaintSystems.PaintSystemCode, ";
strSQL = strSQL + "SupplierPaintProducts.SupplierPaintProductCode, ";
strSQL = strSQL + "SupplierProductGroups.SupplierProductGroupCode ";
strSQL = strSQL + "FROM JobProgress ";
strSQL = strSQL + "INNER JOIN Jobs ON JobProgress.ProgressJobId = Jobs.JobId ";
strSQL = strSQL + "INNER JOIN WorkOrders ON Jobs.WorkOrderId = WorkOrders.WorkOrderId ";
strSQL = strSQL + "INNER JOIN PaintSystems ON Jobs.PaintSystemId = PaintSystems.PaintSystemId ";
strSQL = strSQL + "INNER JOIN SupplierPaintProducts ON Jobs.SupplierPaintProductId = SupplierPaintProducts.SupplierPaintProductId ";
strSQL = strSQL + "INNER JOIN SupplierProductGroups ON SupplierPaintProducts.SupplierProductgroupId = SupplierProductGroups.SupplierProductGroupId ";
strSQL = strSQL + "WHERE JobProgress.ProgressJobId = " + jobId.ToString() + " ";
strSQL = strSQL + "ORDER BY Jobs.ScheduleDate, Jobs.ScheduleSeq";
System.Data.SqlClient.SqlCommand cmdGet = new System.Data.SqlClient.SqlCommand(strSQL, myVPSConnection);
System.Data.SqlClient.SqlDataReader rdrGet = cmdGet.ExecuteReader();
if (rdrGet.HasRows == true)
{
myMCProgress.Load(rdrGet);
recordCount = myMCProgress.Rows.Count;
}
rdrGet.Close();
cmdGet.Dispose();
}
catch (Exception ex)
{
ErrorMessage = "Get Multi-Coat Job Progress Records - " + ex.Message;
}
return(recordCount);
}
public Double Get_Job_Area(Int32 jobId, String areaType)
{
Double myArea = 0;
System.Data.DataTable myDetails = new System.Data.DataTable();
ErrorMessage = string.Empty;
try
{
String strSQL = "SELECT * FROM JobDetails ";
strSQL = strSQL + "INNER JOIN WorkOrderDetails ON JobDetails.WorkOrderDetailId = WorkOrderDetails.WorkorderDetailId ";
strSQL = strSQL + "WHERE Jobdetails.Jobid = " + jobId.ToString();
System.Data.SqlClient.SqlCommand cmdGet = new System.Data.SqlClient.SqlCommand(strSQL, myVPSConnection);
System.Data.SqlClient.SqlDataReader rdrGet = cmdGet.ExecuteReader();
if (rdrGet.HasRows == true)
{
myDetails.Clear();
myDetails.Load(rdrGet);
}
rdrGet.Close();
cmdGet.Dispose();
for (int i = 0; i < myDetails.Rows.Count; i++)
{
if (areaType == "AP")
{
myArea = myArea + Convert.ToInt32(myDetails.Rows[i]["Qty"]) * Convert.ToDouble(myDetails.Rows[i]["UnitAreaPrice"]);
}
else
{
myArea = myArea + Convert.ToInt32(myDetails.Rows[i]["Qty"]) * Convert.ToDouble(myDetails.Rows[i]["UnitAreaStock"]);
}
}
}
catch (Exception ex)
{
ErrorMessage = "Get Job Area - " + ex.Message;
}
return(myArea);
}
public ActionResult ExportExcelDataReporte()
{
var data = (EncuestaDTO)Session["reporte"];
System.Data.DataTable dt = new System.Data.DataTable();
dt.Clear();
//int npre = data.listaReportePreguntas.Count;
//int nper = data.listaReportePersonas.Count;
dt.Columns.Add("Preguntas");
foreach (var persona in data.listaReportePersonas)
dt.Columns.Add(persona.Nombre);
dt.Columns.Add("Promedio por pregunta");
List<SeccionDTO> indices = new List<SeccionDTO>();
int cont = 7;
int tempIdSeccion = 0;
//Tabla Preguntas y Evaluados Detalle
//for (int pre = 0; pre < npre; pre++)
foreach (var pregunta in data.listaReportePreguntas)
{
if (tempIdSeccion == 0)
{
SeccionDTO nuevaSeccion = new SeccionDTO();
nuevaSeccion.Valor = cont;
//nuevaSeccion.Nombre = data.listaReportePreguntas[pre].NombreSeccion;
nuevaSeccion.Nombre = pregunta.NombreSeccion;
indices.Add(nuevaSeccion);
cont = 0;
}
else
{
if (tempIdSeccion != 0 && tempIdSeccion != pregunta.IdSeccion && pregunta.IdTipoRespuesta == 3)//data.listaReportePreguntas[pre].IdSeccion)
{
SeccionDTO nuevaSeccion = new SeccionDTO();
nuevaSeccion.Valor = indices[indices.Count - 1].Valor + cont + 1;
//nuevaSeccion.Nombre = data.listaReportePreguntas[pre].NombreSeccion;
nuevaSeccion.Nombre = pregunta.NombreSeccion;
indices.Add(nuevaSeccion);
cont = 0;
}
}
tempIdSeccion = pregunta.IdSeccion;
//tempIdSeccion = data.listaReportePreguntas[pre].IdSeccion;
System.Data.DataRow row = dt.NewRow();
//row[0] = data.listaReportePreguntas[pre].Texto;
if(pregunta.IdTipoRespuesta == 3)
row["Preguntas"] = pregunta.Texto;
//for (int j = 0; j < nper; j++)
foreach (var evaluado in data.listaReportePersonas)
{
var promedio = data.listaReporteDetalle.Where(x => x.IdPregunta == pregunta.IdPregunta && x.IdEvaluado == evaluado.IdPersona && x.IdTipoRespuesta == 3).Select(x => x.PromedioPreguntaXEvaluado).SingleOrDefault();
row[evaluado.Nombre] = promedio.ToString(CultureInfo.InvariantCulture);
}
if (pregunta.IdTipoRespuesta == 3)
{
//Agregar Promedio General x pregunta
row["Promedio por pregunta"] = pregunta.Promedio.ToString(CultureInfo.InvariantCulture);
dt.Rows.Add(row);
}
cont++;
}
//Seccion para los Promedios por Persona
SeccionDTO aux = new SeccionDTO();
aux.Valor = indices[indices.Count - 1].Valor + cont + 1;
aux.Nombre = "Promedio por persona";
indices.Add(aux);
//Row de Promedios generales por persona
System.Data.DataRow rowPromPer = dt.NewRow();
rowPromPer["Preguntas"] = "Promedios Generales";
//for(int i=0; i<nper; i++)
foreach (var evaluado in data.listaReportePersonas)
{
rowPromPer[evaluado.Nombre] = evaluado.Promedio.ToString(CultureInfo.InvariantCulture);
}
dt.Rows.Add(rowPromPer);
GridView gv = new GridView();
gv.DataSource = dt;
gv.AllowPaging = false;
gv.DataBind();
if (dt.Rows.Count > 0)
{
AddSuperHeader(gv, "INFORME REPORTES 2014");
//Cabecera principal
AddWhiteHeader(gv, 1, "");
AddWhiteHeader(gv, 2, "PROMEDIO GENERAL : " + data.PromedioGeneral.ToString(CultureInfo.InvariantCulture));
AddWhiteHeader(gv, 3, "CANTIDAD EVALUADORES : " + data.CantEvaluadores.ToString());
AddWhiteHeader(gv, 4, "CANTIDAD ENCUESTAS RESUELTAS : " + data.CantEncuestasResueltas.ToString());
AddWhiteHeader(gv, 5, "");
foreach (var item in indices)
{
AddHeader(gv, item.Valor, item.Nombre);
}
//Change the Header Row back to white color
gv.HeaderRow.Style.Add("background-color", "#FFFFFF");
//Applying stlye to gridview header cells
for (int i = 0; i < gv.HeaderRow.Cells.Count; i++)
{
gv.HeaderRow.Cells[i].Style.Add("background-color", "#b8bbc2");
}
//This loop is used to apply stlye to cells based on particular row
foreach (GridViewRow gvrow in gv.Rows)
{
gvrow.BackColor = System.Drawing.Color.White;
gvrow.Style.Add("vertical-align", "middle");
}
//for para no mostrar el row de comentarios
Response.ClearContent();
Response.Buffer = true;
Response.AddHeader("content-disposition", "attachment; filename=Reporte de Encuestas.xls");
Response.ContentType = "application/ms-excel";
Response.Charset = "";
StringWriter sw = new StringWriter();
HtmlTextWriter htw = new HtmlTextWriter(sw);
gv.RenderControl(htw);
Response.Output.Write(sw.ToString());
Response.Flush();
Response.End();
htw.Close();
sw.Close();
}
return View(); //RedirectToAction("Procesos");
}
