/// <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();
}