这里写自定义目录标题
给公司做的一个小工具,单纯的记录的一下。不做参考,因为写的很差。
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.Text.RegularExpressions;
using System.Threading;
using System.Threading.Tasks;
using System.Windows.Forms;
namespace 定额反查清单刷库工具
{
public partial class Form1 : Form
{
OpenFileDialog File = new OpenFileDialog();
private string listingID = ""; //指引库ListingID
private string wcID = ""; //指引库WCID
private string rationRange = "";//指引库RationRange
private string qdID = ""; //清单库ID
private string qdBH = ""; //清单库BH
SQLiteConnection con; //数据库连接
private delegate void DelProgressBar(int i);
public delegate void printString(string str);
public delegate bool readString(string str);
public Form1()
{
InitializeComponent();
}
/// <summary>
/// 清单库路径
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btn_GetQDK_Click(object sender, EventArgs e)
{
OpenFileFrom(File);
tb_QDKPAth.Text = File.FileName; //将文件路径显示到texBox
}
/// <summary>
/// 定额库路径
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void bt_GetDEK_Click(object sender, EventArgs e)
{
OpenFileFrom(File);
tb_DEKPath.Text = File.FileName; //将文件路径显示到texBox
}
/// <summary>
/// 指引库路径
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void bt_GetZYK_Click(object sender, EventArgs e)
{
OpenFileFrom(File);
tb_ZYKPath.Text = File.FileName; //将文件路径显示到texBox
}
//ProgressBarForm progressBarForm = new ProgressBarForm();
/// <summary>
/// 开始刷库
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void bt_Start_Click(object sender, EventArgs e)
{
ActionBtn(bt_Start.Text);
}
/// <summary>
/// 打开文件窗口
/// </summary>
private void OpenFileFrom(OpenFileDialog File)
{
File.Multiselect = false;
File.ShowDialog();
}
/// <summary>
/// 操作数据库
/// </summary>
/// <param 数据库路径="DBPath"></param>
/// <param sql语句="sqlStr"></param>
/// <returns></returns>
private SQLiteDataReader OperationDatabase(string DBPath, string sqlStr)
{
DBPath = @"Data Source=" + DBPath;
con = new SQLiteConnection(DBPath);
if (con.State != ConnectionState.Open)
{
con.Open();
SQLiteCommand cmd = con.CreateCommand();
cmd.CommandText = sqlStr;
SQLiteDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
return dr;
}
return null;
}
/// <summary>
/// 是否有英文字符
/// </summary>
/// <param name="Ration"></param>
/// <returns></returns>
private bool isExists(string Ration)
{
return Regex.Matches(Ration, "[a-zA-Z]").Count > 0;
}
/// <summary>
/// 数据库操作
/// </summary>
/// <param name="progressBarForm"></param>
private void DatabaseLookup(ProgressBarForm progressBarForm)
{
printString pr = new printString(PrintString.WriteToFile);
readString rs = new readString(PrintString.OpenFileWs);
try
{
//1.获取指引库ListingID,WCID,RationRange
var zykDr = OperationDatabase(tb_ZYKPath.Text, @"SELECT *FROM RATIONGUIDE");
while (zykDr.Read())
{
listingID = zykDr["ListingID"].ToString();
wcID = zykDr["WCID"].ToString();
rationRange = zykDr["RationRange"].ToString();
//2.获取清单库BH
var qdkDr = OperationDatabase(tb_QDKPAth.Text + ";Password=cx.com?ss=838773", @"SELECT *FROM LISTING WHERE ID =" + listingID);
while (qdkDr.Read())
{
qdID = qdkDr["ID"].ToString();
if (listingID == qdID)
{
qdBH = qdkDr["BH"].ToString();
}
//3.获取定额库BhBm
var dekDr = OperationDatabase(tb_DEKPath.Text + ";Password=cx.com?ss=838773", @"SELECT *FROM Ration");
while (dekDr.Read())
{
string bhbm = dekDr["BhBm"].ToString();
string fcqdbh = dekDr["FCQDBH"].ToString();
if(rationRange.Contains("~~") || rationRange.Contains(","))
{
pr.Invoke(rationRange);
break;
}
List<string> strList = new List<string>();
if (rationRange.Contains(",") && !isExists(rationRange)) //定额范围包含,
{
try
{
var spStr = rationRange.Split(',');
foreach (var sp in spStr)
{
if (!sp.Contains("~"))
strList.Add(sp);
else
{
var split = sp.Split('~');
var subStr02 = split[0].Substring(0, 2);
if(subStr02 == "02") //安装
{
var subStr0 = split[0].Substring(0, 6);
var subStr1 = split[1].Substring(0, 6);
if (subStr0 != subStr1)
{
var ret = rs.Invoke(rationRange);
if (ret)
pr.Invoke(rationRange);
break;
}
if (split[0].Length > 9 || split[1].Length > 9)
{
var ret = rs.Invoke(rationRange);
if (ret)
pr.Invoke(rationRange);
break;
}
}
else //建筑,市政,园林
{
var subStr0 = split[0].Substring(0, 4);
var subStr1 = split[1].Substring(0, 4);
if (subStr0 != subStr1)
{
var ret = rs.Invoke(rationRange);
if (ret)
pr.Invoke(rationRange);
break;
}
}
if(split[0] == split[1])
{
var ret = rs.Invoke(rationRange);
if (ret)
pr.Invoke(rationRange);
break;
}
var s0 = int.Parse(split[0]);
var s1 = int.Parse(split[1]);
if (s0 - s1 > 0)
{
var ret = rs.Invoke(rationRange);
if (ret)
pr.Invoke(rationRange);
break;
}
foreach (var s in split)
{
var intS = int.Parse(s);
strList.Add("0" + intS.ToString());
while (true) //范围内的定额要先创建出来
{
intS++;
if (intS == int.Parse(split[1]))
{
break;
}
strList.Add("0" + intS.ToString());
}
strList.Add(split[1]);
break;
}
}
}
}
catch
{
progressBarForm.Close();
MessageBox.Show(rationRange, "这条数据出错了,修改后再执行刷库11");
return;
}
}
else if (rationRange.Contains("~") && !isExists(rationRange)) //定额范围包含~
{
try
{
var spStr = rationRange.Split('~');
foreach (var s in spStr)
{
var subS = spStr[0].Substring(0, 2);
if (subS == "02")
{
var subStr0 = spStr[0].Substring(0, 6);
var subStr1 = spStr[1].Substring(0, 6);
if (subStr0 != subStr1)
{
var ret = rs.Invoke(rationRange);
if (ret)
pr.Invoke(rationRange);
break;
}
}
else
{
var subStr0 = spStr[0].Substring(0, 4);
var subStr1 = spStr[1].Substring(0, 4);
if (subStr0 != subStr1)
{
var ret = rs.Invoke(rationRange);
if(ret)
pr.Invoke(rationRange);
break;
}
}
var s0 = int.Parse(spStr[0]);
var s1 = int.Parse(spStr[1]);
if(s0 - s1 > 0)
{
var ret = rs.Invoke(rationRange);
if (ret)
pr.Invoke(rationRange);
pr.Invoke(rationRange);
break;
}
var intS = int.Parse(s);
strList.Add("0" + intS.ToString());
while (true) //范围内的定额要先创建出来
{
intS++;
if (intS == int.Parse(spStr[1]))
{
break;
}
strList.Add("0" + intS.ToString());
}
strList.Add(spStr[1]);
break;
}
}
catch
{
progressBarForm.Close();
MessageBox.Show(rationRange, "这条数据出错了,修改后再执行刷库22");
return;
}
}
foreach (var str in strList)
{
if (bhbm == str)
{
try
{
if (fcqdbh == "")
{
//更新定额库raiton表FCQDBH字段
SQLiteCommand("UPDATE RATION SET FCQDBH =" + "'" + qdBH + "'" + " WHERE BHBM = '" + str + "'");
}
else
{
if (!fcqdbh.Contains(qdBH))
{
//更新定额库raiton表FCQDBH字段
SQLiteCommand("UPDATE RATION SET FCQDBH = FCQDBH ||'" + ',' + qdBH + "' WHERE BHBM = '" + str + "'");
}
}
}
catch
{
progressBarForm.Close();
MessageBox.Show(rationRange, "这条数据出错了,修改后再执行刷库33");
return;
}
}
}
if (bhbm == rationRange)
{
try
{
if(fcqdbh != null)
//更新定额库raiton表FCQDBH字段
SQLiteCommand("UPDATE RATION SET FCQDBH =" + "'" + qdBH + "'" + " WHERE BHBM = '" + rationRange + "'");
}
catch
{
progressBarForm.Close();
MessageBox.Show(rationRange, "这条数据出错了");
return;
}
}
}
}
}
con.Close();
progressBarForm.Close();
MessageBox.Show("刷库完成");
}
catch
{
progressBarForm.Close();
MessageBox.Show("数据表不存在,或字段错误","提示");
return;
}
}
private void btn_Delete_Click(object sender, EventArgs e)
{
ActionBtn(btn_Delete.Text);
}
/// <summary>
/// 操作数据库
/// </summary>
/// <param name="progressBarForm"></param>
private void DatabaseLookup2(ProgressBarForm progressBarForm)
{
con = new SQLiteConnection(@"Data Source = " + tb_ZYKPath.Text);
con.Open();
//先判断数据表是否存在
string sqlExist = "SELECT count(*) FROM sqlite_master WHERE type='table' AND name='oldrationguide'";
SQLiteCommand ExistCmd = new SQLiteCommand(sqlExist, con);
object count = ExistCmd.ExecuteScalar();
if (Convert.ToInt32(count) == 1)
{
progressBarForm.Close();
MessageBox.Show("数据表已存在,请先删除原先的数据表再创建");
return;
}
//重命名rationguide表
SQLiteCommand("ALTER TABLE rationguide RENAME TO oldrationguide");
//创建rationguide表
SQLiteCommand("CREATE TABLE rationguide (ID INT,listingID INT,WCID INT ,RationRange TEXT)");
int i = 0;
//获取清单库 ListWorkContents表数据
var qdkDr = OperationDatabase(tb_QDKPAth.Text + ";Password=cx.com?ss=838773", @"SELECT *FROM LISTINGWORKCONTENTS");
while (qdkDr.Read())
{
var listId = qdkDr["ListingID"].ToString();
var wcId = qdkDr["WorkContentsID"].ToString();
//获取指引库 rationguide表数据
var zykDr = OperationDatabase(tb_ZYKPath.Text, @"SELECT *FROM oldrationguide ORDER BY LISTINGID ASC");
while (zykDr.Read())
{
var listid = zykDr["ListingID"].ToString();
var wcid = zykDr["WCID"].ToString();
var rationrange = zykDr["RationRange"].ToString();
if (listId == listid && wcId == wcid) //listingID与wcid同时满足
{
i++;
//把匹配到的数据插入新的表格中
SQLiteCommand("INSERT INTO rationguide (ID,ListingID,WCID,RATIONRANGE)" + "VALUES('" + i + "','" + listid + "','" + wcid + "','" + rationrange + "')");
}
}
}
con.Close();
progressBarForm.Close();
MessageBox.Show("已删除不匹配的定额范围");
}
/// <summary>
/// 执行操作按钮
/// </summary>
/// <param name="btn_name"></param>
private void ActionBtn(string btn_name)
{
if (tb_QDKPAth.Text == "" || tb_QDKPAth.Text == "" || tb_ZYKPath.Text == "")
{
MessageBox.Show("库路径不可为空");
return;
}
ProgressBarForm progressBarForm = new ProgressBarForm();
DelProgressBar delBar = new DelProgressBar(progressBarForm.showPro);
progressBarForm.Show();
Task.Factory.StartNew(() =>
{
for (int k = 0; k < 100; k++)
{
k++;
delBar.Invoke(k);
Thread.Sleep(4000);
}
});
if(btn_name == "定额范围表匹配")
{
Task.Factory.StartNew(() =>
{
DatabaseLookup2(progressBarForm);
});
}
if(btn_name == "开始刷库")
{
Task.Factory.StartNew(() =>
{
DatabaseLookup(progressBarForm);
});
}
}
/// <summary>
/// 数据库命令
/// </summary>
/// <param name="cmdStr"></param>
private void SQLiteCommand(string cmdStr)
{
SQLiteCommand cmd = con.CreateCommand();
cmd.CommandText = cmdStr;
cmd.ExecuteNonQuery();
}
}
}
using System.Windows.Forms;
namespace 定额反查清单刷库工具
{
public partial class ProgressBarForm : Form
{
public ProgressBarForm()
{
CheckForIllegalCrossThreadCalls = false;
InitializeComponent();
progressBar1.Value = 0;
progressBar1.Minimum = 0;
progressBar1.Maximum = 100;
}
public void showPro(int value)
{
if (progressBar1.Value <= progressBar1.Maximum)
{
progressBar1.Value = value;
}
}
}
}
using System;
using System.IO;
namespace 定额反查清单刷库工具
{
class PrintString
{
static FileStream fs;
static StreamWriter sw;
static StreamReader sr;
static string filePath = $“{Environment.CurrentDirectory}” + “\ErrorRation.txt”;
public static void WriteToFile(string s)
{
fs = new FileStream(filePath, FileMode.Append, FileAccess.Write);
sw = new StreamWriter(fs);
sw.WriteLine(s);
sw.Flush();
sw.Close();
fs.Close();
}
public static bool OpenFileWs(string s)
{
fs = new FileStream(filePath, FileMode.Open);
sr = new StreamReader(fs);
string line;
while((line = sr.ReadLine()) != null)
{
if ((line = sr.ReadLine()) == s)
{
sr.Close();
fs.Close();
return false;
}
}
sr.Close();
fs.Close();
return true;
}
}
}