/// <summary> /// Функция для включения/выключения ТМЦ (Принимает сформированный лист и параметр enable = true/включить, false/выключить ) /// </summary> /// <param name="?"></param> void EnableKmat(List <string> kmats, bool enable) { if (!InfoManager.YesNo("Вход EnableKmat(kmats)")) { return; } int k = 0; for (int i = 0; i < kmats.Count; i++) { var OldKmat = KsmTable.GetRecord(kmats[i]); var tablePOD_OLD = PodTable.GetRecord(OldKmat.Kskl); var upd = SqlClient.Main.CreateUpdateBuilder(); upd.Table.Name = "KSM"; if (enable) { upd.Set.SetValue("PR_DO", ""); } // Включить else { upd.Set.SetValue("PR_DO", "О"); } // Выключить upd.Where = new SqlCmdText("KSM.kmat=@kmat", new SqlParam("kmat", kmats[i])); upd.Exec(); } }
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; } } }