/// <summary> /// This is a simple (and lazy, read: effective) solution. Simply send your /// object to Newtonsoft serialize method, with the indented formatting, and /// you have your own Dump() extension method. /// </summary> /// <typeparam name="T">The object Type</typeparam> /// <param name="anObject">The object to dump</param> /// <param name="aTitle">Optional, will print this before the dump.</param> /// <returns>The object as you passed it</returns> public static T Dump <T>(this T anObject, string aTitle = "") { var pretty_json = JsonConvert.SerializeObject(anObject, Formatting.Indented); if (aTitle != "") { //System.Diagnostics.Debug.WriteLine(aTitle + ": "); //Console.Out.WriteLine(aTitle + ": "); } //System.Diagnostics.Debug.WriteLine(pretty_json.Replace("\"", "'"));//Console.Out.WriteLine(pretty_json); InfoManager.MessageBox(new MessageBoxParams(pretty_json.Replace("\"", "'")) { Caption = aTitle }); return(anObject); }
bool InsertBalanceDMZ(out decimal undoc, out string ndm_s, int CEH, string N_KDK, string fileName) { bool res = false; undoc = 0; ndm_s = ""; try { decimal cnt = 1; undoc = SqlClient.CreateCommand("Select LAST_NOM From LSTN Where Alias = 'DMR ' ").ExecScalar <decimal>() + cnt; decimal ndm = SqlClient.CreateCommand(string.Format("Select LAST_NOM From LSTN Where ALIAS = 'DMZ' and LSTNOBJ = 'CO000CBALOBORM201712' ")).ExecScalar <decimal>() + cnt; ndm_s = ndm.ToString(); //if (!InfoManager.YesNo("undoc = " + undoc.ToString() + "\n" + "ndm = " + ndm.ToString())) return; SqlClient.CreateCommand(string.Format("Update LSTN Set LAST_NOM = @undoc Where ALIAS = 'DMR ' "), new SqlParam("undoc", undoc)).ExecNonQuery(); SqlClient.CreateCommand(string.Format("Update LSTN Set LAST_NOM = @undoc Where ALIAS = 'DMZ' and LSTNOBJ = 'CO000CBALOBORM201712' "), new SqlParam("undoc", ndm)).ExecNonQuery(); // //DateTime DT = DateTime.Now; //DateTime DT = new DateTime(2017, 10, 31); // DateTime DT = new DateTime(2018, 11, 1); DateTime DT = new DateTime(2019, 08, 1); var ib = SqlClient.Main.CreateInsertBuilder(); ib.TableName = "DMZ"; ib.AddValue("UNDOC", undoc); ib.AddValue("GODMEC", "201811"); ib.AddValue("DDM", DT); ib.AddValue("NDM", ndm_s.ToString()); //ib.AddValue("KDMT", "_BALANCES"); ib.AddValue("KDMT", "BALOBOR"); ib.AddValue("KSTO", 0); ib.AddValue("KOBJ_CR", "CO000"); ib.AddValue("KOBJ_DB", "CO000"); ib.AddValue("CEH_K", CEH); ib.AddValue("N_KDK_K", N_KDK); // Получатель - N_KDK_UMC ib.AddValue("ORG_2", 1); ib.AddValue("NBNK2", 10); ib.AddValue("ORG_GPO_2", 1); ib.AddValue("KVAL", 980); ib.AddValue("KKVT", "NB"); ib.AddValue("KURS", 1); ib.AddValue("PRRA", "+"); ib.AddValue("KSD2", "05"); ib.AddValue("KSTO", 246); ib.AddValue("COMM", fileName); ib.AddValue("KBLS", "UKR"); ib.AddValue("FIO_D", "ADM"); ib.AddValue("DATE_D", DateTime.Now); ib.Exec(); FixStageBusinessProcess(undoc); res = true; return(res); } catch (Exception ex) { res = false; InfoManager.MessageBox("False DMZ"); return(res); } }
public void OpenExcel(string path, string fileName) { OleDbConnection connection; try { if (!InfoManager.YesNo("OpenExcel_")) { return; } decimal undoc; string ndm_s; string BS = "00223"; // Забалонсовый счет string[] stringSeparator = new string[] { " - " }; string[] result = fileName.Split(stringSeparator, StringSplitOptions.RemoveEmptyEntries); int cehDMZ = Convert.ToInt32(result[1].ToString()); string n_kdkDMZ = result[2].ToString(); string connectionString; //'Для Excel 12.0 connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + path + fileName + "; Extended Properties=\"Excel 12.0 Xml;HDR=Yes\";"; connection = new OleDbConnection(connectionString); connection.Open(); OleDbCommand command = connection.CreateCommand(); command.CommandText = "Select * From [sheet$A0:I15000] "; //Where [К-во (факт)] = 20 "; OleDbDataAdapter da = new OleDbDataAdapter(command); DataTable dt = new DataTable(); da.Fill(dt); //if (!InfoManager.YesNo("InsertBalanceDMZ")) return; if (!InsertBalanceDMZ(out undoc, out ndm_s, cehDMZ, n_kdkDMZ, fileName)) { InfoManager.MessageBox("DMZ не вставилась!!!"); return; } //if (!InfoManager.YesNo("Вставка прошла DMZ")) return; //if (!InfoManager.YesNo("Подготовка на вставку строк DMS")) return; RowTXT rowTXT = new RowTXT(); rowTXT.CloneTable(dt); List <string> DoubleKmat = new List <string>(); int k = 0; foreach (DataRow row in dt.Rows) { //if (!InfoManager.YesNo("Прдолжить добавление строк?")) return; try { string ceh_s = row["ceh"].ToString(); string kmat_old = row["kmat"].ToString(); //string kmat = ConvertKmat(kmat_old, ceh_s); string kmat = ConvertKmat(kmat_old, ceh_s, DoubleKmat); bool flag1 = false; object[] arrayColumn = row.ItemArray; if (Flag(arrayColumn)) { break; } int ceh = Convert.ToInt32(ceh_s); string n_kdk = row["n_kdk"].ToString(); string naim = row["naim"].ToString(); string size_type = row["size_type"].ToString(); int ei = FuncEI(row["ei"].ToString()); decimal price = FuncPrice(row["price"].ToString()); decimal count = FuncCount(row["count"].ToString()); //InfoManager.MessageBox("row['sum'].ToString() = " + row["sum"].ToString()); decimal sum = FuncSum(row["sum"].ToString()); #region " ShowKmat " //StringBuilder sb = new StringBuilder(); //sb.Append(ceh.ToString() + "\n"); //sb.Append(kmat.ToString() + "\n"); //sb.Append(naim.ToString() + "\n"); //sb.Append(size_type.ToString() + "\n"); //sb.Append(ei.ToString() + "\n"); //sb.Append(price.ToString() + "\n"); //sb.Append(count.ToString() + "\n"); //sb.Append(sum.ToString() + "\n"); ////InfoManager.MessageBox(sb.ToString()); //if (!InfoManager.YesNo(sb.ToString())) return; #endregion //================================================================= try { if (!DoubleKmat.Contains(kmat_old) | kmat_old == "") { DoubleKmat.Add(kmat_old); DataRow[] rows1 = null; try { rows1 = dt.Select("kmat = '" + kmat_old + "' and naim = '" + naim + "'"); } catch (Exception) { rows1 = dt.Select("kmat = " + kmat_old + " and naim = '" + naim + "'"); } int flag = 0; if (rows1.Count() > 1) { flag = 1; for (int i = 0; i < rows1.Count(); i++) { DataRow r1 = rows1[i]; naim = r1["naim"].ToString(); size_type = r1["size_type"].ToString(); ei = FuncEI(r1["ei"].ToString()); price = FuncPrice(r1["price"].ToString()); count = FuncCount(r1["count"].ToString()); sum = FuncSum(r1["sum"].ToString()); if (i == 0) { string ss1 = rows1[0].ItemArray[2].ToString(); string ss2 = rows1[0].ItemArray[3].ToString(); string sss = rows1.ToString(); if (!KsmTable.IsRecord(kmat)) { InsertKmat(kmat, kmat_old, naim, size_type, ei, fileName, BS); } try { InsertBalanceDMS(undoc, ndm_s, ceh, n_kdk, kmat, ei, count, price, sum, BS); } catch (Exception) { if (!InfoManager.YesNo("Insert False")) { return; } } //KSM.Add(kmat, ss2); //Console.WriteLine(String.Format("{0}\t{1}\t{2}\t {3}\t{4}\t{5}\t{6}\t{7}\t{8}\t{9}", k, ceh, kmat, kmat_old.PadRight(12), naim.PadRight(30), size_type.PadRight(20), ei, price, count, sum)); } else { try { InsertBalanceDMS(undoc, ndm_s, ceh, n_kdk, kmat, ei, count, price, sum, BS); } catch (Exception) { if (!InfoManager.YesNo("Insert False")) { return; } } //Console.WriteLine(String.Format("{0}\t{1}\t{2}\t {3}\t{4}\t{5}\t{6}\t{7}\t{8}\t{9}", k, ceh, kmat, kmat_old.PadRight(12), naim.PadRight(30), size_type.PadRight(20), ei, price, count, sum)); } } } DataRow[] rows2 = null; try { rows2 = dt.Select("kmat = '" + kmat_old + "'"); } catch (Exception) { rows2 = dt.Select("kmat = " + kmat_old); } if (flag == 0) { if (rows2.Count() > 1) { for (int i = 0; i < rows2.Count(); i++) { DataRow r2 = rows2[i]; naim = r2["naim"].ToString(); size_type = r2["size_type"].ToString(); ei = FuncEI(r2["ei"].ToString()); price = FuncPrice(r2["price"].ToString()); count = FuncCount(r2["count"].ToString()); sum = FuncSum(r2["sum"].ToString()); if (i == 0) { //naim = r2["naim"].ToString(); //size_type = r2["size_type"].ToString(); if (!KsmTable.IsRecord(kmat)) { InsertKmat(kmat, kmat_old, naim, size_type, Convert.ToInt32(ei), fileName, BS); } try { InsertBalanceDMS(undoc, ndm_s, ceh, n_kdk, kmat, Convert.ToInt32(ei), count, price, sum, BS); } catch (Exception) { if (!InfoManager.YesNo("Insert False")) { return; } } //Console.WriteLine(String.Format("{0}\t{1}\t{2}\t {3}\t{4}\t{5}\t{6}\t{7}\t{8}\t{9}", k, ceh, kmat, kmat_old.PadRight(12), naim.PadRight(30), size_type.PadRight(20), ei, price, count, sum)); //} } else { counter++; kmat = ConvertKmat("", ceh_s, DoubleKmat); if (!KsmTable.IsRecord(kmat)) { InsertKmat(kmat, kmat_old, naim, size_type, Convert.ToInt32(ei), fileName, BS); } try { InsertBalanceDMS(undoc, ndm_s, ceh, n_kdk, kmat, Convert.ToInt32(ei), count, price, sum, BS); } catch (Exception) { if (!InfoManager.YesNo("Insert False")) { return; } } //Console.WriteLine(String.Format("{0}\t{1}\t{2}\t {3}\t{4}\t{5}\t{6}\t{7}\t{8}\t{9}", k, ceh, kmat, kmat_old.PadRight(12), naim.PadRight(30), size_type.PadRight(20), ei, price, count, sum)); } } } else { //string ss1 = rows2[0].ItemArray[2].ToString(); //string ss2 = rows2[0].ItemArray[3].ToString(); //if (!KsmTable.IsRecord(kmat)) //{ //InsertKmat(kmat, kmat_old, naim, size_type, Convert.ToInt32(ei), fileName, BS); if (kmat_old == "") { counter++; kmat = ConvertKmat("", ceh_s, DoubleKmat); //kmat = ConvertKmat("", ceh_s); } if (!KsmTable.IsRecord(kmat)) { InsertKmat(kmat, kmat_old, naim, size_type, Convert.ToInt32(ei), fileName, BS); } try { InsertBalanceDMS(undoc, ndm_s, ceh, n_kdk, kmat, Convert.ToInt32(ei), count, price, sum, BS); } catch (Exception) { if (!InfoManager.YesNo("Insert False")) { return; } } //Console.WriteLine(String.Format("{0}\t{1}\t{2}\t {3}\t{4}\t{5}\t{6}\t{7}\t{8}\t{9}", k, ceh, kmat, kmat_old.PadRight(12), naim.PadRight(30), size_type.PadRight(20), ei, price, count, sum)); } } //else // InfoManager.MessageBox(String.Format("Кода {0} нет!", kmat)); //KSM.Add(rowKmat["kmat"].ToString(), rowKmat["kmat"].ToString()); //Console.WriteLine(String.Format("{0}\t{1}\t{2}\t {3}\t{4}\t{5}\t{6}\t{7}", k, ceh, kmat, kmat_old, ei, price, count, sum)); } } catch (Exception) { InfoManager.MessageBox("Ошибка!"); //DoubleKmat.Add(kmat_old); //var rowsKmat = dt.AsEnumerable().Where(x => x["kmat"].ToString() == kmat_old); var rowsKmat = dt.Select("kmat = '" + kmat_old + "' "); foreach (var rowKmat in rowsKmat) { rowTXT.Add(rowKmat, dt.Rows.IndexOf(rowKmat) + 2); } } //================================================================= //if (!InfoManager.YesNo("InsertKSM " + kmat)) return; //if (!InfoManager.YesNo("InsertBalanceDMS")) return; } catch (Exception ex) { //if (!InfoManager.YesNo(ex.Message)) return; bool flag = false; object[] ddd = row.ItemArray; for (int i = 0; i < ddd.Length - 1; i++) { flag = ddd[i].ToString() == "" ? true : false; } if (flag) { break; } rowTXT.Add(row, k); } } rowTXT.WriteTXT(path, fileName, n_kdkDMZ); try { connection.Close(); } catch (Exception ex) { if (!InfoManager.YesNo("Ошибка:\n" + ex.Message)) { return; } } } catch (Exception ex) { if (!InfoManager.YesNo("Ошибка:\n" + ex.Message)) { return; } } }
public void Test(string fileName) { if (!InfoManager.YesNo("Test")) { return; } //string path = @"\\erp\TEMP\App\Остатки\"; string path = @"\\erp\TEMP\App\Остатки\20.12.17\"; //string path = @"\\erp\TEMP\App\Остатки\540\"; //string path = @"\\erp\TEMP\App\Остатки\17.02.18\"; // //string fileName = "010 - 13004 - 1120665 - Лопатіна д"; //string extension = "xlsx"; string connectionString; OleDbConnection connection; //connectionString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + path + fileName + "." + extension + "; Extended Properties=\"Excel 12.0 Xml;HDR=Yes\";"; connectionString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + path + fileName + "; Extended Properties=\"Excel 12.0 Xml;HDR=Yes\";"; connection = new OleDbConnection(connectionString); connection.Open(); OleDbCommand command = connection.CreateCommand(); command.CommandText = "Select * From [sheet$A0:I15000] "; //Where [К-во (факт)] = 20 "; OleDbDataAdapter da = new OleDbDataAdapter(command); DataTable dt = new DataTable(); da.Fill(dt); //int s = dt.AsEnumerable().Count(); try { var rows2 = dt.Select().AsEnumerable().Select(x => x["kmat"].ToString() == "001-100012").Single(); InfoManager.MessageBox(rows2.ToString()); //IEnumerable<DataTable> dd = (IEnumerable<DataTable>)dt; //System.Data.EnumerableRowCollection<System.Data.DataRow> dd = dt; //System.Data.EnumerableRowCollection<System.Data.DataRow> dd; //DataRow rowKmat = dt.AsEnumerable().Single(x => x["kmat"].ToString() == kmat_old); var rows = dt.Select("kmat = '001-100006' "); StringBuilder sb = new StringBuilder(); //ceh n_kdk kmat naim size_type ei price count sum sb.AppendLine("Строк: " + rows.Count()); foreach (DataRow row in rows) { sb.AppendLine("kmat = " + row["kmat"].ToString()); sb.AppendLine("naim = " + row["naim"].ToString()); } InfoManager.MessageBox(sb.ToString()); InfoManager.MessageBox("Ok"); } catch (Exception) { InfoManager.MessageBox("No"); } //var objects = connection.Close(); }