private void button1_Click(object sender, EventArgs e)
{
try
{
string sql = null;
string data = null;
int i = 0;
int j = 0;
Exce.Application xlApp;
Exce.Workbook xlWorkBook;
Exce.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlApp = new Exce.Application();
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Exce.Worksheet)xlWorkBook.Worksheets.get_Item(1);
connection.Open();
sql = "select re_no, re_date, c_name, payment_type, invoice_type, payment_mode, ref_no, ref_date, in_no, in_date, total_amount, due_amount, receive_amount, notes, total_receive from payment_receipt where (due_amount <> '0')";
OleDbDataAdapter dscmd = new OleDbDataAdapter(sql, connection);
DataSet ds = new DataSet();
dscmd.Fill(ds);
xlWorkSheet.Cells[1, 1] = "Receipt No";
xlWorkSheet.Cells[1, 2] = "Receipt Date";
xlWorkSheet.Cells[1, 3] = "Customer Name";
xlWorkSheet.Cells[1, 4] = "Payment Type";
xlWorkSheet.Cells[1, 5] = "Invoice Type";
xlWorkSheet.Cells[1, 6] = "Payment Mode";
xlWorkSheet.Cells[1, 7] = "Reference No";
xlWorkSheet.Cells[1, 8] = "Reference Date";
xlWorkSheet.Cells[1, 9] = "Invoice No";
xlWorkSheet.Cells[1, 10] = "Invoice Date";
xlWorkSheet.Cells[1, 11] = "Total Amount";
xlWorkSheet.Cells[1, 12] = "Due Amount";
xlWorkSheet.Cells[1, 13] = "Receive Amount";
xlWorkSheet.Cells[1, 14] = "Notes";
xlWorkSheet.Cells[1, 15] = "Total Receive Amount";
for (i = 0; i <= ds.Tables[0].Rows.Count - 1; i++)
{
for (j = 0; j <= ds.Tables[0].Columns.Count - 1; j++)
{
data = ds.Tables[0].Rows[i].ItemArray[j].ToString();
xlWorkSheet.Cells[i + 2, j + 1] = data;
}
}
xlWorkBook.SaveAs("Payment Due Report.xls", Exce.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Exce.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
MessageBox.Show("Excel file created , you can find the file C:\\Users\\User\\Documents. Payment Due Report.xls");
}
catch (Exception)
{
}
finally
{
connection.Close();
}
}
/// <summary>
/// 将Excel数据导入到List表链中
/// </summary>
/// <param name="xlstable">Excel文件名</param>
/// <param name="xlssheet">工作簿</param>
/// <returns></returns>
protected static bool ImportExcelToList(string ExcelFileName, string xlssheet)
{
try
{
//打开Excel表,并写入DataSet中。
//string xlsfile = ExcelFileName;
//string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" + "data source=" + xlsfile
// + ";Extended Properties='Excel 8.0; HDR=YES; IMEX=1'";
string strConn = "Provider=Microsoft.Jet.OleDb.4.0;" + "data source=" + ExcelFileName
+ ";Extended Properties='Excel 8.0; HDR=YES; IMEX=1'";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
DataSet ds = new DataSet();
OleDbDataAdapter odda = new OleDbDataAdapter("select * from [" + xlssheet + "$]", conn);
odda.Fill(ds, "table1");
conn.Close();
//读取DataSet数据集
ltExcelTags.Clear();
////////////////////////////////////////////////////////////////
System.Data.DataTable dt = ds.Tables[0];
int nAll = dt.Rows.Count;
int nValid = 0;
foreach (System.Data.DataRow dr in dt.Rows)
{
if (dr["TagID"].ToString() != "")
{
WS_ExcelTag exlstag = new WS_ExcelTag();
exlstag.TagID = dr["TagID"].ToString().Trim();
exlstag.UnitCode = dr["UnitCode"].ToString().Trim();
exlstag.TagCode = dr["TagCode"].ToString().Trim();
exlstag.TagDesc = dr["TagDesc"].ToString().Trim();
exlstag.TagType = dr["TagType"].ToString().Trim();
exlstag.TagEngunit = dr["TagEngunit"].ToString().Trim();
exlstag.TagIsValid = dr["TagIsValid"].ToString().Trim();
exlstag.TagRunExp = dr["TagRunExp"].ToString().ToUpper().Trim();
exlstag.TagShiftExp = dr["TagShiftExp"].ToString().ToUpper().Trim();
exlstag.TagPeriodExp = dr["TagPeriodExp"].ToString().ToUpper().Trim();
exlstag.TagMWExp = dr["TagMWExp"].ToString().ToUpper().Trim();
exlstag.TagSnapExp = dr["TagSnapExp"].ToString().ToUpper().Trim();
exlstag.TagTargetExp = dr["TagTargetExp"].ToString().ToUpper().Trim();
exlstag.TagScoreExp = dr["TagScoreExp"].ToString().ToUpper().Trim();
exlstag.TagPeriodExp = dr["TagPeriodExp"].ToString().ToUpper().Trim();
//sub tag
ltExcelTags.Add(exlstag);
nValid += 1;
}
}
string strInfor = "Excel表总数为：{0}个, 【ID不为空】有效点为:{1}个。";
strInfor = string.Format(strInfor, nAll, nValid);
LogUtil.LogMessage(strInfor);
return(true);
}
catch (Exception ex)
{
LogUtil.LogMessage("Excel表读取失败:" + ex.ToString());
return(false);
}
}
/// <summary>
/// 获取Excel数据
/// </summary>
/// <param name="sheetName"></param>
/// <param name="fileName"></param>
/// <returns></returns>
public static DataTable GetExcelTable(string sheetName, string fileName)
{
if (string.IsNullOrEmpty(sheetName))
{
string[] names = GetExcelSheetNames(fileName);
if (names == null)
{
sheetName = "sheet1";
}
else
{
sheetName = names[0];
}
}
if (!sheetName.EndsWith("$"))
{
sheetName = sheetName + "$";
}
DataSet ds = null;
string connString = string.Empty;
string sql = "";
connString = GetExcelConnString(fileName);
sql = "SELECT * FROM [" + sheetName + "]";
OleDbConnection conn = null;
OleDbDataAdapter cmd = null;
try
{
conn = new OleDbConnection(connString);
conn.Open();
cmd = new OleDbDataAdapter(sql, connString);
ds = new DataSet();
cmd.Fill(ds, "TABLE1");
}
catch (Exception ex)
{
string msg = "GetExcelTable：读取Excel出现错误\r\n";
if (ex.Message.IndexOf("外部表不是预期的格式") > 0)
{
msg += "非Excel2003格式\r\n";
}
throw new ApplicationException(msg + ex.Message);
}
finally
{
if (conn != null)
{
conn.Close();
conn.Dispose();
}
}
if (ds != null && ds.Tables.Count > 0)
{
return(ds.Tables[0]);
}
return(null);
}
/// <summary>
/// 从指定（绝对路径）Excel文件读取相应的数据到DataTable中
/// Excel文件不能加密
/// </summary>
/// <param name="excelFile">Excel文件名（绝对路径）</param>
/// <param name="SelectCols">拟读取的列名，默认为*</param>
/// <param name="where">过滤条件</param>
/// <param name="orderby">排序字段</param>
/// <returns></returns>
public static DataTable GetDataFromExcel(string excelFile, string SelectCols = "", string where = "", string orderby = "")
{
DataTable result = new DataTable();
try
{
FileInfo file = new FileInfo(excelFile);
if (!file.Exists)
{
throw new Exception(excelFile + " 文件不存在");
}
string extension = file.Extension;
string cnnString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelFile + ";Extended Properties='Excel 12.0 XML;HDR=YES;IMEX=1;';";
if (extension == ".xls")
{
cnnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + excelFile + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'";
}
OleDbConnection dbCnn = new OleDbConnection(cnnString);
dbCnn.Open();
result = dbCnn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
if (result != null && result.Rows.Count > 0)
{
result.TableName = result.Rows[0]["TABLE_NAME"].ToString();
}
string sql = SelectCols;
if (sql.Trim().Length == 0)
{
sql = "Select * ";
}
else
{
sql = "Select " + sql;
}
sql = sql + " from [" + result + "A:BU] ";
if (where.Trim().Length > 0)
{
sql = sql + " Where " + where;
}
if (orderby.Trim().Length > 0)
{
sql = sql + " Order by " + orderby;
}
OleDbCommand cmd = new OleDbCommand(sql, dbCnn);
System.Data.OleDb.OleDbDataAdapter da = new OleDbDataAdapter(cmd);
result = new DataTable();
da.Fill(result);
dbCnn.Close();
dbCnn = null;
GC.Collect();
return(result);
}
catch (Exception err)
{
throw err;
}
}
public DataTable GetDataTable(string SQLQuery)
{
DataTable dt = new DataTable();
if (SQLQuery == "")
{
dt = null;
}
else
{
switch (_DBType)
{
case 1:
case 2:
using (SqlConnection sqlConn = new SqlConnection(SystemInfo.ConnStr))
{
sqlConn.Open();
SqlDataAdapter sqlDA = new SqlDataAdapter(SQLQuery, sqlConn);
if (sqlDA.SelectCommand != null)
{
sqlDA.SelectCommand.CommandTimeout = CommandTimeout;
}
if (sqlDA.DeleteCommand != null)
{
sqlDA.DeleteCommand.CommandTimeout = CommandTimeout;
}
if (sqlDA.UpdateCommand != null)
{
sqlDA.UpdateCommand.CommandTimeout = CommandTimeout;
}
sqlDA.Fill(dt);
sqlDA.Dispose();
sqlDA = null;
}
break;
case 255:
using (OleDbConnection oleConn = new OleDbConnection(SystemInfo.ConnStr))
{
oleConn.Open();
OleDbDataAdapter oleDA = new OleDbDataAdapter(SQLQuery, oleConn);
if (oleDA.SelectCommand != null)
{
oleDA.SelectCommand.CommandTimeout = CommandTimeout;
}
if (oleDA.DeleteCommand != null)
{
oleDA.DeleteCommand.CommandTimeout = CommandTimeout;
}
if (oleDA.UpdateCommand != null)
{
oleDA.UpdateCommand.CommandTimeout = CommandTimeout;
}
oleDA.Fill(dt);
oleDA.Dispose();
oleDA = null;
}
break;
}
}
return(dt);
}
public override void RunCommand(object sender)
{
//create engine, instance, query
var engine = (AutomationEngineInstance)sender;
var query = v_Query.ConvertUserVariableToString(engine);
//define connection
var databaseConnection = (OleDbConnection)v_InstanceName.GetAppInstance(engine);
var queryExecutionType = v_QueryType.ConvertUserVariableToString(engine);
//define commad
var oleCommand = new OleDbCommand(query, databaseConnection);
//add parameters
foreach (DataRow rw in v_QueryParameters.Rows)
{
var parameterName = rw.Field <string>("Parameter Name").ConvertUserVariableToString(engine);
var parameterValue = rw.Field <string>("Parameter Value").ConvertUserVariableToString(engine);
var parameterType = rw.Field <string>("Parameter Type").ConvertUserVariableToString(engine);
object convertedValue = null;
//"STRING", "BOOLEAN", "DECIMAL", "INT16", "INT32", "INT64", "DATETIME", "DOUBLE", "SINGLE", "GUID", "BYTE", "BYTE[]"
switch (parameterType)
{
case "STRING":
convertedValue = parameterValue;
break;
case "BOOLEAN":
convertedValue = Convert.ToBoolean(parameterValue);
break;
case "DECIMAL":
convertedValue = Convert.ToDecimal(parameterValue);
break;
case "INT16":
convertedValue = Convert.ToInt16(parameterValue);
break;
case "INT32":
convertedValue = Convert.ToInt32(parameterValue);
break;
case "INT64":
convertedValue = Convert.ToInt64(parameterValue);
break;
case "DATETIME":
convertedValue = Convert.ToDateTime(parameterValue);
break;
case "DOUBLE":
convertedValue = Convert.ToDouble(parameterValue);
break;
case "SINGLE":
convertedValue = Convert.ToSingle(parameterValue);
break;
case "GUID":
convertedValue = Guid.Parse(parameterValue);
break;
case "BYTE":
convertedValue = Convert.ToByte(parameterValue);
break;
case "BYTE[]":
convertedValue = System.Text.Encoding.UTF8.GetBytes(parameterValue);
break;
default:
throw new NotImplementedException($"Parameter Type '{parameterType}' not implemented!");
}
oleCommand.Parameters.AddWithValue(parameterName, convertedValue);
}
if (queryExecutionType == "Return Dataset")
{
DataTable dataTable = new DataTable();
OleDbDataAdapter adapter = new OleDbDataAdapter(oleCommand);
adapter.SelectCommand = oleCommand;
databaseConnection.Open();
adapter.Fill(dataTable);
databaseConnection.Close();
dataTable.TableName = v_OutputUserVariableName;
engine.DataTables.Add(dataTable);
dataTable.StoreInUserVariable(engine, v_OutputUserVariableName);
}
else if (queryExecutionType == "Execute NonQuery")
{
databaseConnection.Open();
var result = oleCommand.ExecuteNonQuery();
databaseConnection.Close();
result.ToString().StoreInUserVariable(engine, v_OutputUserVariableName);
}
else if (queryExecutionType == "Execute Stored Procedure")
{
oleCommand.CommandType = CommandType.StoredProcedure;
databaseConnection.Open();
var result = oleCommand.ExecuteNonQuery();
databaseConnection.Close();
result.ToString().StoreInUserVariable(engine, v_OutputUserVariableName);
}
else
{
throw new NotImplementedException($"Query Execution Type '{queryExecutionType}' not implemented.");
}
}
protected void btnSave_Click(object sender, EventArgs e)
{
string dirURL = "~/files/file/Stock/";
string conn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + MapPath(dirURL) + uplaodFile() + "';Extended Properties='Excel 12.0;HDR=Yes';";
// string conn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='G:/Anam/Projects/Mavrick/gentlepark/Offline Version/Code/HO/V5/POS/Stock" + uplaodFile() + "';Extended Properties='Excel 12.0;HDR=Yes';";
OleDbConnection objConn = new OleDbConnection(conn);
objConn.Open();
OleDbCommand objCmdSelect = new OleDbCommand("SELECT * FROM [Sheet1$]", objConn);
OleDbDataAdapter objAdapter = new OleDbDataAdapter();
objAdapter.SelectCommand = objCmdSelect;
DataSet objDataset = new DataSet();
objAdapter.Fill(objDataset);
objConn.Close();
GridView1.DataSource = objDataset.Tables[0];
GridView1.DataBind();
string sql = @"
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Table_1]') AND type in (N'U'))
DROP TABLE [dbo].[Table_1]
CREATE TABLE [dbo].[Table_1](
[ProductName] [nvarchar](256) NULL,
[Barcode] [nvarchar](50) NULL,
[Style] [nvarchar](50) NULL,
[Size] [nvarchar](50) NULL,
[Qty] [decimal](18, 2) NULL,
[Price] [decimal](18, 2) NULL
)
delete Table_1;
";
string ex = "";
decimal total = 0;
CommonManager.SQLExec(sql);
foreach (GridViewRow gvr in GridView1.Rows)
{
try
{
if (int.Parse(gvr.Cells[4].Text) > 0)
{
sql = @"
INSERT INTO [GentlePark].[dbo].[Table_1]
([ProductName]
,[Barcode]
,[Style]
,[Size]
,[Qty]
,[Price])
VALUES
('" + gvr.Cells[1].Text + @"'--<ProductName, nvarchar(256),>
,'" + gvr.Cells[0].Text + @"'--<Barcode, nvarchar(50),>
,'" + gvr.Cells[2].Text + @"'--<Style, nvarchar(50),>
,'" + gvr.Cells[3].Text + @"'--<Size, nvarchar(50),>
," + gvr.Cells[4].Text + @"--<Qry, decimal(18,2),>
," + decimal.Parse(gvr.Cells[5].Text).ToString("0.00") + @"--<price, decimal(18,2),>
); ";
total += (decimal.Parse(gvr.Cells[4].Text) * decimal.Parse(gvr.Cells[5].Text));
CommonManager.SQLExec(sql);
}
}
catch (Exception exep)
{
ex += gvr.Cells[0].Text + ",";
}
}
#region Brnach
try
{
sql = "/*" + ex + total + @"*/
USE GentlePark
Declare @ProductName nvarchar(256)
Declare @ProductID int
Declare @Pos_ProductID int
Declare @BarCode nvarchar(256)
Declare @Style nvarchar(256)
Declare @Size nvarchar(256)
Declare @Pos_SizeID int
Declare @ExtraField1 nvarchar(256)
declare @Price decimal(10,2)
Declare @WorkStationID int
set @WorkStationID=" + ddlShowRoom.SelectedValue + @"
Declare @Pos_TransactionMasterID int
Set @Pos_TransactionMasterID=" + int.Parse(ddlShowRoom.SelectedItem.Text.Split('-')[1]).ToString() + @"
DECLARE product_cursor CURSOR FOR
SELECT ProductName,Barcode,Qty,Price,Style,Size
FROM Table_1
--where cast(Qty as int) >0
OPEN product_cursor
FETCH NEXT FROM product_cursor INTO @ProductName,@BarCode,@ExtraField1,@Price,@Style,@Size
WHILE @@FETCH_STATUS = 0
BEGIN
Set @ProductID = (Select Count(*) from ACC_ChartOfAccountLabel4 where ChartOfAccountLabel4Text=@ProductName and ACC_HeadTypeID=3)
if @ProductID = 0
BEGIN
--Create Product Name
INSERT INTO [ACC_ChartOfAccountLabel4]
([Code]
,[ACC_HeadTypeID]
,[ChartOfAccountLabel4Text]
,[ExtraField1]
,[ExtraField2]
,[ExtraField3]
,[AddedBy]
,[AddedDate]
,[UpdatedBy]
,[UpdatedDate]
,[RowStatusID])
VALUES
(''
,3
,@ProductName
,SUBSTRING(@BarCode,1,5)
,''
,'@'
,1
,GETDATE()
,1
,GETDATE()
,1)
END
Set @ProductID = (select top 1 ACC_ChartOfAccountLabel4ID from ACC_ChartOfAccountLabel4 where ChartOfAccountLabel4Text=@ProductName and ACC_HeadTypeID=3)
--add Product
Set @Pos_ProductID = (Select Count(*) from Pos_Product where BarCode=@BarCode)
if @Pos_ProductID = 0
BEGIN
if(select Count(Pos_SizeID) from Pos_Size where SizeName =@Size) =0
BEGIN
set @Pos_SizeID= 46
END
ELSE
BEGIN
set @Pos_SizeID =(select top 1 Pos_SizeID from Pos_Size where SizeName =@Size)
END
INSERT INTO [Pos_Product]
([ProductID]
,[ReferenceID]
,[Pos_ProductTypeID]
,[Inv_UtilizationDetailsIDs]
,[ProductStatusID]
,[ProductName]
,[DesignCode]
,[Pos_SizeID]
,[Pos_BrandID]
,[Inv_QuantityUnitID]
,[FabricsCost]
,[AccesoriesCost]
,[Overhead]
,[OthersCost]
,[PurchasePrice]
,[SalePrice]
,[OldSalePrice]
,[Note]
,[BarCode]
,[Pos_ColorID]
,[Pos_FabricsTypeID]
,[StyleCode]
,[Pic1]
,[Pic2]
,[Pic3]
,[VatPercentage]
,[IsVatExclusive]
,[DiscountPercentage]
,[DiscountAmount]
,[FabricsNo]
,[ExtraField1]
,[ExtraField2]
,[ExtraField3]
,[ExtraField4]
,[ExtraField5]
,[ExtraField6]
,[ExtraField7]
,[ExtraField8]
,[ExtraField9]
,[ExtraField10]
,[AddedBy]
,[AddedDate]
,[UpdatedBy]
,[UpdatedDate]
,[RowStatusID])
VALUES
(@ProductID
,1
,1
,''
,1
,@ProductName
,''
,@Pos_SizeID
,1
,3
,0
,0
,0
,0
,0
,@Price
,0
,'Stock entry-BR'
,@BarCode
,1
,1
,@Style
,''
,''
,''
,0
,1
,0
,0
,''
,'0'
,''
,''
,''
,''
,''
,''
,''
,''
,''
,1
,GETDATE()
,1
,GETDATE()
,1);
Set @Pos_ProductID = (Select top 1 Pos_ProductID from Pos_Product where BarCode=@BarCode)
END
Set @Pos_ProductID = (Select top 1 Pos_ProductID from Pos_Product where BarCode=@BarCode)
--Production Transaction
INSERT INTO [Pos_Transaction]
([Pos_ProductID]
,[Quantity]
,[Pos_ProductTrasactionTypeID]
,[Pos_ProductTransactionMasterID]
,[WorkStationID]
,[ExtraField1]
,[ExtraField2]
,[ExtraField3]
,[ExtraField4]
,[ExtraField5]
,[AddedBy]
,[AddedDate]
,[UpdatedBy]
,[UpdatedDate]
,[RowStatusID])
VALUES
(@Pos_ProductID
,Cast(@ExtraField1 as decimal(10,2))
,1
,21
,1
,''
,''
,''
,''
,''
,1
,GETDATE()
,1
,GETDATE()
,1)
--accounts need to update
--Issue Transaction
INSERT INTO [Pos_Transaction]
([Pos_ProductID]
,[Quantity]
,[Pos_ProductTrasactionTypeID]
,[Pos_ProductTransactionMasterID]
,[WorkStationID]
,[ExtraField1]
,[ExtraField2]
,[ExtraField3]
,[ExtraField4]
,[ExtraField5]
,[AddedBy]
,[AddedDate]
,[UpdatedBy]
,[UpdatedDate]
,[RowStatusID])
VALUES
(@Pos_ProductID
,Cast(@ExtraField1 as decimal(10,2))
,9
,@Pos_TransactionMasterID
,@WorkStationID
,''
,''
,''
,''
,''
,1
,GETDATE()
,1
,GETDATE()
,1)
Declare @Count int
set @Count=
(
select COUNT(*) from Pos_WorkStationStock
where ProductID=@Pos_ProductID and WorkStationID=@WorkStationID
)
if @Count = 0
BEGIN
INSERT INTO [Pos_WorkStationStock]
([WorkStationID]
,[ProductID]
,[Stock])
VALUES(@WorkStationID,@Pos_ProductID ,CAST(@ExtraField1 as Decimal(10,2)));
END
ELSE
BEGIN
Update Pos_WorkStationStock set Stock += CAST(@ExtraField1 as Decimal(10,2)) where ProductID=@Pos_ProductID and WorkStationID=@WorkStationID;
END
FETCH NEXT FROM product_cursor INTO @ProductName,@BarCode,@ExtraField1,@Price,@Style,@Size
END
CLOSE product_cursor
DEALLOCATE product_cursor
";
}
catch (Exception ex3)
{ }
#endregion
#region Centeral
string sql_HeadOffice;
sql_HeadOffice = "/*" + ex + total + @"*/
USE GentlePark
Declare @ProductName nvarchar(256)
Declare @ProductID int
Declare @Pos_ProductID int
Declare @BarCode nvarchar(256)
Declare @Style nvarchar(256)
Declare @Size nvarchar(256)
Declare @Pos_SizeID int
Declare @ExtraField1 nvarchar(256)
declare @Price decimal(10,2)
Declare @WorkStationID int
set @WorkStationID=1
Declare @Pos_TransactionMasterID int
Set @Pos_TransactionMasterID=20
DECLARE product_cursor CURSOR FOR
SELECT ProductName,Barcode,Qty,Price,Style,Size
FROM Table_1
--where cast(Qty as int) >0
OPEN product_cursor
FETCH NEXT FROM product_cursor INTO @ProductName,@BarCode,@ExtraField1,@Price,@Style,@Size
WHILE @@FETCH_STATUS = 0
BEGIN
Set @ProductID = (Select Count(*) from ACC_ChartOfAccountLabel4 where ChartOfAccountLabel4Text=@ProductName and ACC_HeadTypeID=3)
if @ProductID = 0
BEGIN
--Create Product Name
INSERT INTO [ACC_ChartOfAccountLabel4]
([Code]
,[ACC_HeadTypeID]
,[ChartOfAccountLabel4Text]
,[ExtraField1]
,[ExtraField2]
,[ExtraField3]
,[AddedBy]
,[AddedDate]
,[UpdatedBy]
,[UpdatedDate]
,[RowStatusID])
VALUES
(''
,3
,@ProductName
,SUBSTRING(@BarCode,1,5)
,''
,'@'
,1
,GETDATE()
,1
,GETDATE()
,1)
END
Set @ProductID = (select top 1 ACC_ChartOfAccountLabel4ID from ACC_ChartOfAccountLabel4 where ChartOfAccountLabel4Text=@ProductName and ACC_HeadTypeID=3)
--add Product
Set @Pos_ProductID = (Select Count(*) from Pos_Product where BarCode=@BarCode)
if @Pos_ProductID = 0
BEGIN
if(select Count(Pos_SizeID) from Pos_Size where SizeName =@Size) =0
BEGIN
set @Pos_SizeID= 46
END
ELSE
BEGIN
set @Pos_SizeID =(select top 1 Pos_SizeID from Pos_Size where SizeName =@Size)
END
INSERT INTO [Pos_Product]
([ProductID]
,[ReferenceID]
,[Pos_ProductTypeID]
,[Inv_UtilizationDetailsIDs]
,[ProductStatusID]
,[ProductName]
,[DesignCode]
,[Pos_SizeID]
,[Pos_BrandID]
,[Inv_QuantityUnitID]
,[FabricsCost]
,[AccesoriesCost]
,[Overhead]
,[OthersCost]
,[PurchasePrice]
,[SalePrice]
,[OldSalePrice]
,[Note]
,[BarCode]
,[Pos_ColorID]
,[Pos_FabricsTypeID]
,[StyleCode]
,[Pic1]
,[Pic2]
,[Pic3]
,[VatPercentage]
,[IsVatExclusive]
,[DiscountPercentage]
,[DiscountAmount]
,[FabricsNo]
,[ExtraField1]
,[ExtraField2]
,[ExtraField3]
,[ExtraField4]
,[ExtraField5]
,[ExtraField6]
,[ExtraField7]
,[ExtraField8]
,[ExtraField9]
,[ExtraField10]
,[AddedBy]
,[AddedDate]
,[UpdatedBy]
,[UpdatedDate]
,[RowStatusID])
VALUES
(@ProductID
,1
,1
,''
,1
,@ProductName
,''
,@Pos_SizeID
,1
,3
,0
,0
,0
,0
,0
,@Price
,0
,'Stock entry-BR'
,@BarCode
,1
,1
,@Style
,''
,''
,''
,0
,1
,0
,0
,''
,@ExtraField1
,''
,''
,''
,''
,''
,''
,''
,''
,''
,1
,GETDATE()
,1
,GETDATE()
,1);
Set @Pos_ProductID = (Select top 1 Pos_ProductID from Pos_Product where BarCode=@BarCode)
--Transaction
INSERT INTO [Pos_Transaction]
([Pos_ProductID]
,[Quantity]
,[Pos_ProductTrasactionTypeID]
,[Pos_ProductTransactionMasterID]
,[WorkStationID]
,[ExtraField1]
,[ExtraField2]
,[ExtraField3]
,[ExtraField4]
,[ExtraField5]
,[AddedBy]
,[AddedDate]
,[UpdatedBy]
,[UpdatedDate]
,[RowStatusID])
VALUES
(@Pos_ProductID
,Cast(@ExtraField1 as decimal(10,2))
,1
,@Pos_TransactionMasterID
,1
,''
,''
,''
,''
,''
,1
,GETDATE()
,1
,GETDATE()
,1)
--accounts need to update
END
Set @Pos_ProductID = (Select top 1 Pos_ProductID from Pos_Product where BarCode=@BarCode)
update Pos_Product set ExtraField1=@ExtraField1 where Pos_ProductID=@Pos_ProductID
FETCH NEXT FROM product_cursor INTO @ProductName,@BarCode,@ExtraField1,@Price,@Style,@Size
END
CLOSE product_cursor
DEALLOCATE product_cursor
";
#endregion
txtCursor.Text = sql;
CommonManager.SQLExec(sql_HeadOffice);
//CommonManager.SQLExec(sql);
//DataSet ds = MSSQL.SQLExec(sql + "; select Count(*) from Mem_Fees where Comn_PaymentByID=2");
//Label1.Text = ds.Tables[0].Rows.Count + " Record(s) Added Successfully";
//btnSave.Visible = false;
}
public static (ExpensePerOperation exp, bool success) ReadData(string samplingGuid, bool readComplete = false)
{
ExpensePerOperation exp = new ExpensePerOperation(samplingGuid);
bool success = false;
string sql = $"Select * from tblFishingExpense where SamplingGuid ={{{samplingGuid}}}";
using (var con = new OleDbConnection(global.ConnectionString))
{
using (var dt = new DataTable())
{
con.Open();
var adapter = new OleDbDataAdapter(sql, con);
adapter.Fill(dt);
if (dt.Rows.Count > 0)
{
DataRow dr = dt.Rows[0];
if (double.TryParse(dr["CostOfFishing"].ToString(), out double costOfFishing))
{
SamplingExpenses = $"Cost of fishing: {costOfFishing.ToString()}\r\n";
exp.CostOfFishing = costOfFishing;
}
else
{
SamplingExpenses = $"Cost of fishing: -\r\n";
}
if (double.TryParse(dr["ReturnOfInvestment"].ToString(), out double roi))
{
exp.ReturnOfInvestment = roi;
SamplingExpenses += $"Return of investment: {roi.ToString()}\r\n";
}
else
{
SamplingExpenses += $"Return of investment: -\r\n";
}
if (double.TryParse(dr["IncomeFromFishSold"].ToString(), out double income))
{
exp.IncomeFromFishSale = income;
SamplingExpenses += $"Income from fish sales: {income.ToString()}\r\n";
}
else
{
SamplingExpenses += $"Income from fish sales: -\r\n";
}
if (double.TryParse(dr["FishWeightForConsumption"].ToString(), out double weightFishConsumed))
{
exp.WeightFishConsumed = weightFishConsumed;
SamplingExpenses += $"Weight of fish consumed: {weightFishConsumed.ToString()}";
}
else
{
SamplingExpenses += $"Weight of fish consumed: -";
}
success = true;
}
else
{
SamplingExpenses = "Cost of fishing: -\r\nReturn of investment: -\r\nIncome from fish sales: -\r\nWeight of fish consumed: -";
}
}
if (success)
{
Dictionary <string, FishingExpenseItemsPerOperation> expenseItemsList = new Dictionary <string, FishingExpenseItemsPerOperation>();
sql = $"Select * from tblFishingExpenseItems where SamplingGuid={{{samplingGuid}}}";
//using (var con1 = new OleDbConnection(global.ConnectionString))
//{
using (var dt = new DataTable())
{
//con1.Open();
//var adapter = new OleDbDataAdapter(sql, con1);
var adapter = new OleDbDataAdapter(sql, con);
adapter.Fill(dt);
if (dt.Rows.Count > 0)
{
SamplingExpenses += "\r\n\r\nCost items:\r\n";
foreach (DataRow dr in dt.Rows)
{
string key = dr["ExpenseRow"].ToString();
string item = dr["ExpenseItem"].ToString();
double cost = (double)dr["Cost"];
string unit = dr["Unit"].ToString();
double?unitQuantity = null;
if (double.TryParse(dr["UnitQuantity"].ToString(), out double v))
{
unitQuantity = v;
}
FishingExpenseItemsPerOperation expenseItem = new FishingExpenseItemsPerOperation(key, item, cost, unit, unitQuantity, fad3DataStatus.statusFromDB);
SamplingExpenses += $"{item}: {cost.ToString()}\r\n";
exp.AddExpenseItem(key, expenseItem);
}
}
}
//}
}
return(exp, success);
}
}
public DataSet fetch(string query)
{
OleDbTransaction trans = this.conn.BeginTransaction();
OleDbCommand comm = new OleDbCommand();
comm.CommandText = query;
comm.Connection = this.conn;
comm.Transaction = trans;
OleDbDataAdapter adapter = new OleDbDataAdapter();
adapter.SelectCommand = comm;
DataSet ds = new DataSet();
adapter.Fill(ds, "tbl_0");
trans.Commit();
return ds;
}
private void button1_Click(object sender, EventArgs e)
{
try
{
//BoxCash
double Deposit = 0;
double Withdraw = 0;
double BoxCash = 0;
OleDbDataAdapter da = new OleDbDataAdapter("select Deposit,Withdraw from BoxInfo", con);
DataTable dt = new DataTable();
da.Fill(dt);
for (int i = 0; i < dt.Rows.Count; i++)
{
Deposit += double.Parse(dt.Rows[i][0].ToString());
Withdraw += double.Parse(dt.Rows[i][1].ToString());
}
BoxCash = Deposit - Withdraw;
//**********************************************************
//CusAcount
double Cus_Am_Rem = 0;
double C_A_Pay = 0;
double CusAcount = 0;
OleDbDataAdapter da1 = new OleDbDataAdapter("select Cus_Am_Rem from Cus_Account", con);
DataTable dt1 = new DataTable();
da1.Fill(dt1);
OleDbDataAdapter da2 = new OleDbDataAdapter("select C_A_Pay from Cus_Pay", con);
DataTable dt2 = new DataTable();
da2.Fill(dt2);
for (int i1 = 0; i1 < dt1.Rows.Count; i1++)
{
Cus_Am_Rem += double.Parse(dt1.Rows[i1][0].ToString());
}
for (int i2 = 0; i2 < dt2.Rows.Count; i2++)
{
C_A_Pay += double.Parse(dt2.Rows[i2][0].ToString());
}
CusAcount = Cus_Am_Rem - C_A_Pay;
//*****************************************************************
//ProValue
double cash = 0;
OleDbDataAdapter da3 = new OleDbDataAdapter("select Qty,Buy_Price,Sell_Price from Product", con);
DataTable dt3 = new DataTable();
da3.Fill(dt3);
for (int i3 = 0; i3 < dt3.Rows.Count; i3++)
{
if (cmbType.Text.Equals("سعر الشراء"))
{
cash += double.Parse(dt3.Rows[i3][0].ToString()) * double.Parse(dt3.Rows[i3][1].ToString());
}
else
{
cash += double.Parse(dt3.Rows[i3][0].ToString()) * double.Parse(dt3.Rows[i3][2].ToString());
}
}
//*******************************************************************
//SupAcount
double Sup_A_Am = 0;
double Sup_Pay = 0;
double SupAcount = 0;
OleDbDataAdapter da4 = new OleDbDataAdapter("select Sup_A_Am from Sup_Account", con);
DataTable dt4 = new DataTable();
da4.Fill(dt4);
OleDbDataAdapter da5 = new OleDbDataAdapter("select Sup_Pay from Sup_Pay", con);
DataTable dt5 = new DataTable();
da5.Fill(dt5);
for (int i4 = 0; i4 < dt4.Rows.Count; i4++)
{
Sup_A_Am += double.Parse(dt4.Rows[i4][0].ToString());
}
for (int i5 = 0; i5 < dt5.Rows.Count; i5++)
{
Sup_Pay += double.Parse(dt5.Rows[i5][0].ToString());
}
SupAcount = Sup_A_Am - Sup_Pay;
//***************************************************************
txtAmount.Text = ((BoxCash + CusAcount + cash) - SupAcount).ToString();
}
catch (Exception ex)
{
MessageBox.Show("0");
}
}
//**************************************************************************
/// <Description>
/// This method uses to get data for C1TrueDBGrid
/// </Description>
/// <Inputs>
/// MasterID, DetailID
/// </Inputs>
/// <Outputs>
/// DataTable
/// </Outputs>
/// <Returns>
/// DataTable
/// </Returns>
/// <Authors>
/// DungLA
/// </Authors>
/// <History>
/// 06-Jan-2005
/// 12/Oct/2005 Thachnn: fix bug injection
/// </History>
/// <Notes>
/// </Notes>
//**************************************************************************
public DataTable GetDataForTrueDBGrid(string pstrMasterID, string pstrDetailID, out bool oblnIsEdit)
{
const string METHOD_NAME = THIS + ".GetDataForTrueDBGrid()";
string strSql = string.Empty;
// create new table with 4 columns to store data and return to caller
DataTable dtblSource = new DataTable(sys_ReportDrillDownTable.TABLE_NAME);
dtblSource.Columns.Add(sys_ReportDrillDownTable.MASTERPARA_FLD);
dtblSource.Columns.Add(sys_ReportDrillDownTable.DETAILPARA_FLD);
dtblSource.Columns.Add(sys_ReportParaTable.DATATYPE_FLD);
dtblSource.Columns.Add(sys_ReportDrillDownTable.FROMCOLUMN_FLD);
dtblSource.Columns.Add(sys_ReportDrillDownTable.PARAORDER_FLD);
// set data type for FromColumn column
dtblSource.Columns[sys_ReportDrillDownTable.FROMCOLUMN_FLD].DataType = typeof(bool);
// set default value for FromColumn
dtblSource.Columns[sys_ReportDrillDownTable.FROMCOLUMN_FLD].DefaultValue = false;
DataSet dstSource = new DataSet();
OleDbConnection oconPCS = null;
OleDbCommand ocmdPCS = null;
#region Existed record in sys_ReportDrillDown table
// first we check for existing record in sys_ReportDrillDown table
strSql = "SELECT " + sys_ReportDrillDownTable.MASTERREPORTID_FLD + ","
+ sys_ReportDrillDownTable.DETAILREPORTID_FLD + ","
+ sys_ReportDrillDownTable.MASTERPARA_FLD + ","
+ sys_ReportDrillDownTable.DETAILPARA_FLD + ","
+ sys_ReportDrillDownTable.FROMCOLUMN_FLD + ","
+ sys_ReportDrillDownTable.PARAORDER_FLD
+ " FROM " + sys_ReportDrillDownTable.TABLE_NAME
+ " WHERE " + sys_ReportDrillDownTable.MASTERREPORTID_FLD + "= ? " //+ pstrMasterID + "'"
+ " AND " + sys_ReportDrillDownTable.DETAILREPORTID_FLD + "= ? "; // + pstrDetailID + "'";
try
{
// if already existed, then get current data
Utils utils = new Utils();
oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
ocmdPCS = new OleDbCommand(strSql, oconPCS);
ocmdPCS.Parameters.Add(new OleDbParameter(sys_ReportDrillDownTable.MASTERREPORTID_FLD, OleDbType.VarWChar));
ocmdPCS.Parameters[sys_ReportDrillDownTable.MASTERREPORTID_FLD].Value = pstrMasterID;
ocmdPCS.Parameters.Add(new OleDbParameter(sys_ReportDrillDownTable.DETAILREPORTID_FLD, OleDbType.VarWChar));
ocmdPCS.Parameters[sys_ReportDrillDownTable.DETAILREPORTID_FLD].Value = pstrDetailID;
ocmdPCS.Connection.Open();
OleDbDataReader odrdPCS = ocmdPCS.ExecuteReader();
if (odrdPCS.HasRows)
{
while (odrdPCS.Read())
{
DataRow drow = dtblSource.NewRow();
drow[sys_ReportDrillDownTable.MASTERPARA_FLD] = odrdPCS[sys_ReportDrillDownTable.MASTERPARA_FLD];
drow[sys_ReportDrillDownTable.DETAILPARA_FLD] = odrdPCS[sys_ReportDrillDownTable.DETAILPARA_FLD];
drow[sys_ReportDrillDownTable.FROMCOLUMN_FLD] = odrdPCS[sys_ReportDrillDownTable.FROMCOLUMN_FLD];
drow[sys_ReportDrillDownTable.PARAORDER_FLD] = odrdPCS[sys_ReportDrillDownTable.PARAORDER_FLD];
dtblSource.Rows.Add(drow);
}
sys_ReportParaDS dsSysReportPara = new sys_ReportParaDS();
foreach (DataRow drow in dtblSource.Rows)
{
drow[sys_ReportParaTable.DATATYPE_FLD] = dsSysReportPara.GetDataType(pstrDetailID, drow[sys_ReportDrillDownTable.DETAILPARA_FLD].ToString().Trim());
}
// return
oblnIsEdit = true;
return(dtblSource);
}
}
catch (OleDbException ex)
{
throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
}
catch (Exception ex)
{
throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
}
finally
{
if (oconPCS != null)
{
if (oconPCS.State != ConnectionState.Closed)
{
oconPCS.Close();
}
}
}
#endregion
oblnIsEdit = false;
#region Not existed, make new data and return to user
/// TODO: Bro.DungLA. Thachnn says: I feel bug here, when creating strSql, but I don't know why and how to fix.
/// The old code is really fusion with continuing plus and plus SQL string.
/// Please comeback to this point when there are any errors.
try
{
Utils utils = new Utils();
oconPCS.ConnectionString = Utils.Instance.OleDbConnectionString;
string strSqlOrder = " ORDER BY " + sys_ReportParaTable.PARAORDER_FLD + " ASC";
string strSqlMaster = "SELECT "
+ sys_ReportParaTable.PARAORDER_FLD + ","
+ sys_ReportParaTable.PARANAME_FLD + ","
+ sys_ReportParaTable.DATATYPE_FLD
+ " FROM " + sys_ReportParaTable.TABLE_NAME
+ " WHERE " + sys_ReportParaTable.REPORTID_FLD + "= ? " + strSqlOrder;
OleDbCommand ocmdMaster = new OleDbCommand(strSqlMaster, oconPCS);
ocmdMaster.Parameters.Add(new OleDbParameter(sys_ReportParaTable.REPORTID_FLD, OleDbType.VarWChar));
ocmdMaster.Parameters[sys_ReportParaTable.REPORTID_FLD].Value = pstrMasterID;
// fill master para into dataset with new table
OleDbDataAdapter odadMaster = new OleDbDataAdapter(ocmdMaster);
odadMaster.Fill(dstSource, sys_ReportDrillDownTable.MASTERPARA_FLD);
string strSqlDetail = "SELECT "
+ sys_ReportParaTable.PARAORDER_FLD + ","
+ sys_ReportParaTable.PARANAME_FLD + ","
+ sys_ReportParaTable.DATATYPE_FLD
+ " FROM " + sys_ReportParaTable.TABLE_NAME
+ " WHERE " + sys_ReportParaTable.REPORTID_FLD + "= ? " + strSqlOrder;
OleDbCommand ocmdDetail = new OleDbCommand(strSqlDetail, oconPCS);
ocmdDetail.Parameters.Add(new OleDbParameter(sys_ReportParaTable.REPORTID_FLD, OleDbType.VarWChar));
ocmdDetail.Parameters[sys_ReportParaTable.REPORTID_FLD].Value = pstrDetailID;
// fill detail para into dataset with new table
OleDbDataAdapter odadDetail = new OleDbDataAdapter(ocmdDetail);
odadDetail.Fill(dstSource, sys_ReportDrillDownTable.DETAILPARA_FLD);
// get all rows
DataRowCollection MasterRows = dstSource.Tables[sys_ReportDrillDownTable.MASTERPARA_FLD].Rows;
DataRowCollection DetailRows = dstSource.Tables[sys_ReportDrillDownTable.DETAILPARA_FLD].Rows;
// if number of master para is bigger than number of detail para
// then the detail para is basic for filling parameter values
if (MasterRows.Count > DetailRows.Count)
{
for (int i = 0; i < MasterRows.Count; i++)
{
DataRow drow = dtblSource.NewRow();
drow[sys_ReportDrillDownTable.MASTERPARA_FLD] = MasterRows[i][sys_ReportParaTable.PARANAME_FLD];
drow[sys_ReportDrillDownTable.PARAORDER_FLD] = i + 1;
if (i < DetailRows.Count)
{
drow[sys_ReportDrillDownTable.DETAILPARA_FLD] = DetailRows[i][sys_ReportParaTable.PARANAME_FLD];
drow[sys_ReportParaTable.DATATYPE_FLD] = DetailRows[i][sys_ReportParaTable.DATATYPE_FLD];
}
else
{
break;
}
dtblSource.Rows.Add(drow);
}
}
// if number of detail para is bigger than number of master para
// then the master para is basic for filling parameter values
else
{
for (int i = 0; i < DetailRows.Count; i++)
{
DataRow drow = dtblSource.NewRow();
drow[sys_ReportDrillDownTable.DETAILPARA_FLD] = DetailRows[i][sys_ReportParaTable.PARANAME_FLD];
drow[sys_ReportDrillDownTable.PARAORDER_FLD] = i + 1;
if (i < MasterRows.Count)
{
drow[sys_ReportDrillDownTable.MASTERPARA_FLD] = MasterRows[i][sys_ReportParaTable.PARANAME_FLD];
drow[sys_ReportParaTable.DATATYPE_FLD] = MasterRows[i][sys_ReportParaTable.DATATYPE_FLD];
}
else
{
drow[sys_ReportDrillDownTable.MASTERPARA_FLD] = null;
drow[sys_ReportParaTable.DATATYPE_FLD] = DetailRows[i][sys_ReportParaTable.DATATYPE_FLD];
}
dtblSource.Rows.Add(drow);
}
}
oblnIsEdit = true;
return(dtblSource);
}
catch (OleDbException ex)
{
throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
}
catch (Exception ex)
{
throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
}
finally
{
if (oconPCS != null)
{
if (oconPCS.State != ConnectionState.Closed)
{
oconPCS.Close();
}
}
}
#endregion
}
public void Convert(IList <string> destinationFolderPaths)
{
var dictionaryConfigurations = GetDictionaryConfigurations();
var connnectionString =
String.Format(
@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0;HDR=No;IMEX=1"";",
_sourceFilePath);
using (var connection = new OleDbConnection(connnectionString))
{
try
{
connection.Open();
}
catch (Exception)
{
throw new ConversionException {
SourceFilePath = _sourceFilePath
};
}
if (connection.State == ConnectionState.Open)
{
{
var dataAdapter = new OleDbDataAdapter(String.Format("SELECT * FROM [{0}$]", SourceSheetName), connection);
var dataTable = new DataTable();
try
{
dataAdapter.Fill(dataTable);
var columnsCount = dataTable.Columns.Count;
if (columnsCount > 0)
{
var processReading = false;
var dictionaryName = String.Empty;
foreach (DataRow row in dataTable.Rows)
{
var rowValue = row[0]?.ToString().Trim() ?? String.Empty;
if (String.IsNullOrEmpty(rowValue))
{
break;
}
if (rowValue.StartsWith("*", StringComparison.OrdinalIgnoreCase))
{
processReading = true;
dictionaryName = rowValue.Replace("*", "");
continue;
}
if (!processReading)
{
continue;
}
var dictionaryConfiguration =
dictionaryConfigurations.FirstOrDefault(configItem => String.Equals(configItem.InputTagName, dictionaryName));
if (dictionaryConfiguration == null)
{
continue;
}
var listDataItem = new ListDataItem
{
Value = rowValue,
IsDefault = String.Equals(row[1]?.ToString().Trim(), "D", StringComparison.OrdinalIgnoreCase),
IsPlaceholder = String.Equals(row[1]?.ToString().Trim(), "PH", StringComparison.OrdinalIgnoreCase)
};
dictionaryConfiguration.ListItems.Add(listDataItem);
}
}
}
catch
{
throw new ConversionException {
SourceFilePath = _sourceFilePath
};
}
finally
{
dataAdapter.Dispose();
dataTable.Dispose();
}
}
}
else
{
throw new ConversionException {
SourceFilePath = _sourceFilePath
}
};
connection.Close();
}
var xml = new StringBuilder();
xml.AppendLine(String.Format("<{0}>", OutputRootNodeName));
foreach (var dictionaryConfiguration in dictionaryConfigurations)
{
foreach (var slideHeader in dictionaryConfiguration.ListItems)
{
xml.Append(String.Format("<{0} ", dictionaryConfiguration.OutputNodeName));
xml.Append("Value = \"" + slideHeader.Value.Replace(@"&", "&").Replace("\"", """) + "\" ");
xml.Append("IsDefault = \"" + slideHeader.IsDefault + "\" ");
xml.Append("IsPlaceholder = \"" + slideHeader.IsPlaceholder + "\" ");
xml.AppendLine(@"/>");
}
}
foreach (var externalLine in ConvertorExtensions.GetExternalOutputLines(
Path.Combine(destinationFolderPaths.First(), OutputFileName),
OutputRootNodeName,
dictionaryConfigurations.Select(configItem => configItem.OutputNodeName).ToArray()))
{
xml.AppendLine(externalLine);
}
xml.AppendLine(String.Format("</{0}>", OutputRootNodeName));
foreach (var folderPath in destinationFolderPaths)
{
var xmlPath = Path.Combine(folderPath, OutputFileName);
using (var sw = new StreamWriter(xmlPath, false))
{
sw.Write(xml.ToString());
sw.Flush();
}
}
}
}
public JsonResult updateExcel(StudentReg studentreg, HttpPostedFileBase FileUpload)
{
List <string> data = new List <string>();
if (FileUpload != null)
{
// tdata.ExecuteCommand("truncate table OtherCompanyAssets");
if (FileUpload.ContentType == "application/vnd.ms-excel" || FileUpload.ContentType == "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
{
string filename = FileUpload.FileName;
string targetpath = Server.MapPath("/DetailFormatInExcel/");
FileUpload.SaveAs(targetpath + filename);
string pathToExcelFile = targetpath + filename;
var connectionString = "";
if (filename.EndsWith(".xls"))
{
connectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0; data source={0}; Extended Properties=Excel 8.0;", pathToExcelFile);
}
else if (filename.EndsWith(".xlsx"))
{
connectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\";", pathToExcelFile);
}
var adapter = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", connectionString);
var ds = new DataSet();
adapter.Fill(ds, "ExcelTable");
DataTable dtable = ds.Tables["ExcelTable"];
string sheetName = "Sheet1";
var excelFile = new ExcelQueryFactory(pathToExcelFile);
var artistAlbums = from a in excelFile.Worksheet <StudentReg>(sheetName) select a;
foreach (DataRow a in dtable.Rows)
{
try
{
if (a["Student Name"] != "" && a["Address"] != "" && a["Contact"] != "")
{
StudentReg TU = new StudentReg();
StudentReg studentreg1 = db.StudentRegs.FirstOrDefault();
if (studentreg1 == null)
{
TU.RollNo = 0001;
}
else
{
var ab = db.StudentRegs.Max(x => x.RollNo);
TU.RollNo = Convert.ToInt32(ab) + 1;
}
//TU.RollNo = Regno(rollno);
//TU.RollNo = a.RollNo;
TU.StudentName = a["Student Name"].ToString();
TU.FatherName = a["Father Name"].ToString();
TU.Address = a["Address"].ToString();
TU.Contact = a["Contact"].ToString();
TU.Laststudy = a["Last study"].ToString();
TU.Medical = a["Medical Done"].ToString();
TU.Refusal = a["Refusal"].ToString();
TU.Email = a["STUDENT E-MAIL ID"].ToString();
TU.Password = a["Password"].ToString();
TU.Fileno = "Jan/19";
db.StudentRegs.Add(TU);
db.SaveChanges();
fees fee = new fees();
fee.studentid = TU.Fileno + "" + TU.RollNo;
fee.Package = Convert.ToInt32(a["Package"]);
fee.Advance = Convert.ToInt32(a["Advance"]);
fee.pay = Convert.ToInt32(a["Advance"]);
fee.balance = fee.Package - fee.pay;
db.fees.Add(fee);
db.SaveChanges();
// a.RollNo, a.StudentName, a.FatherName, a.Address, a.Contact, a.Laststudy, a.Medical, a.Refusal, a.Email, a.Password
}
else
{
//data.Add("<ul>");
//if (a.StudentName == "" || a.StudentName == null) data.Add("<li> name is required</li>");
//if (a.FatherName == "" || a.FatherName == null) data.Add("<li> Father Name is required</li>");
//if (a.Address == "" || a.Address == null) data.Add("<li> Address is required</li>");
//if (a.Contact == "" || a.Contact == null) data.Add("<li>ContactNo is required</li>");
data.Add("</ul>");
data.ToArray();
return(Json(data, JsonRequestBehavior.AllowGet));
}
}
catch (DbEntityValidationException ex)
{
foreach (var entityValidationErrors in ex.EntityValidationErrors)
{
foreach (var validationError in entityValidationErrors.ValidationErrors)
{
Response.Write("Property: " + validationError.PropertyName + " Error: " + validationError.ErrorMessage);
}
}
}
}
//deleting excel file from folder
if ((System.IO.File.Exists(pathToExcelFile)))
{
System.IO.File.Delete(pathToExcelFile);
}
// return RedirectToAction("Index");
return(Json("success", JsonRequestBehavior.AllowGet));
// return RedirectToAction("Index");
// Response.Redirect("Index");
}
else
{
//alert message for invalid file format
data.Add("<ul>");
data.Add("<li>Only Excel file format is allowed</li>");
data.Add("</ul>");
data.ToArray();
return(Json(data, JsonRequestBehavior.AllowGet));
}
}
else
{
data.Add("<ul>");
if (FileUpload == null)
{
data.Add("<li>Please choose Excel file</li>");
}
data.Add("</ul>");
data.ToArray();
return(Json(data, JsonRequestBehavior.AllowGet));
}
}
public void WriteData()
{
string sql = "";
string ConnString = GetConnectionString();
string result = System.Text.RegularExpressions.Regex.Replace(GetInputParamCalculatedValue("ColMappingRules"), @",(?=[^']*'(?:[^']*'[^']*')*[^']*$)", "~^GINGER-EXCEL-COMMA-REPLACE^~");
string[] varColMaps = result.Split(',');
string sSetDataUsed = "";
using (OleDbConnection Conn = new OleDbConnection(ConnString))
{
try
{
Conn.Open();
}
catch (Exception ex)
{
System.Threading.Thread.Sleep(3000);
Conn.Open();
Reporter.ToLog(eLogLevel.ERROR, $"Method - {MethodBase.GetCurrentMethod().Name}, Error - {ex.StackTrace}", ex);
}
OleDbCommand Cmd = new OleDbCommand();
Cmd.Connection = Conn;
string SheetName = GetInputParamCalculatedValue("SheetName").Trim();
if (String.IsNullOrEmpty(SheetName))
{
this.Error += "Sheet Name is empty or not selected. Please Select correct sheet name on action configurations";
Conn.Close();
return;
}
if (!SheetName.EndsWith("$"))
{
SheetName += "$";
}
if (SelectAllRows == false)
{
sql = "Select TOP 1 * from [" + SheetName + "]";
}
else
{
sql = "Select * from [" + SheetName + "]";
}
string where = GetInputParamCalculatedValue("SelectRowsWhere");
if (!string.IsNullOrEmpty(where))
{
sql += " WHERE " + where;
}
Cmd.CommandText = sql;
DataTable dt = new DataTable();
OleDbDataAdapter da = new OleDbDataAdapter();
da.SelectCommand = Cmd;
string updateSQL = "";
try
{
da.Fill(dt);
if (!string.IsNullOrEmpty(GetInputParamCalculatedValue("SetDataUsed")))
{
sSetDataUsed = @", " + GetInputParamCalculatedValue("SetDataUsed");
}
// we expect only 1 record
if (dt.Rows.Count == 1 && SelectAllRows == false)
{
DataRow r = dt.Rows[0];
//Read data to variables
foreach (string vc in varColMaps)
{
string strPrimaryKeyColumn = GetInputParamCalculatedValue("PrimaryKeyColumn");
if (strPrimaryKeyColumn.Contains("`"))
{
strPrimaryKeyColumn = strPrimaryKeyColumn.Replace("`", "");
}
string rowKey = r[strPrimaryKeyColumn].ToString();
int res;
int.TryParse(rowKey, out res);
if (res == 0 || r[strPrimaryKeyColumn].GetType() == typeof(System.String))
{
rowKey = "'" + rowKey + "'";
}
//TODO: fix me in OO Style
//Do mapping
string ColName = vc.Split('=')[0];
string Value = vc.Split('=')[1];
Value = Value.Replace("~^GINGER-EXCEL-COMMA-REPLACE^~", ",");
string txt = Value;
//keeping the translation of vars to support previous implementation
VariableBase var = RunOnBusinessFlow.GetHierarchyVariableByName(Value);
if (var != null)
{
var.Value = ValueExpression.Calculate(var.Value);
txt = var.Value;
}
//remove '' from value
txt = txt.TrimStart(new char[] { '\'' });
txt = txt.TrimEnd(new char[] { '\'' });
//TODO: create one long SQL to do the update in one time and not for each var
updateSQL = @"UPDATE [" + GetInputParamCalculatedValue("SheetName") + "$] SET " +
ColName + " = '" + txt + "'" + sSetDataUsed +
" WHERE " + GetInputParamCalculatedValue("PrimaryKeyColumn") + "=" + rowKey + ";";
this.ExInfo += updateSQL + Environment.NewLine;
OleDbCommand myCommand = new OleDbCommand();
myCommand.Connection = Conn;
myCommand.CommandText = updateSQL;
myCommand.ExecuteNonQuery();
}
// Do the update that row is used
}
else if (dt.Rows.Count > 0 && SelectAllRows == true)
{
updateSQL = @"UPDATE [" + GetInputParamCalculatedValue("SheetName") + "$] SET ";
foreach (string vc in varColMaps)
{
//TODO: fix me in OO Style
//Do mapping
string ColName = vc.Split('=')[0];
string Value = vc.Split('=')[1];
Value = Value.Replace("~^GINGER-EXCEL-COMMA-REPLACE^~", ",");
string txt = Value;
//keeping the translation of vars to support previous implementation
VariableBase var = RunOnBusinessFlow.GetHierarchyVariableByName(Value);
if (var != null)
{
var.Value = ValueExpression.Calculate(var.Value);
if (var != null)
{
txt = var.Value;
}
else
{
txt = Value;
}
}
//remove '' from value
txt = txt.TrimStart(new char[] { '\'' });
txt = txt.TrimEnd(new char[] { '\'' });
//TODO: create one long SQL to do the update in one time and not for each var
updateSQL = updateSQL + ColName + " = '" + txt + "',";
}
updateSQL = updateSQL.Substring(0, updateSQL.Length - 1);
updateSQL = updateSQL + sSetDataUsed;
if (!string.IsNullOrEmpty(where))
{
updateSQL += " WHERE " + where + ";";
}
this.ExInfo += updateSQL + Environment.NewLine;
OleDbCommand myCommand = new OleDbCommand();
myCommand.Connection = Conn;
myCommand.CommandText = updateSQL;
myCommand.ExecuteNonQuery();
}
else if (dt.Rows.Count == 0)
{
this.ExInfo = "No Rows updated with given criteria";
}
}
catch (Exception ex)
{
// Reporter.ToLog(eAppReporterLogLevel.ERROR, "Writing into excel got error " + ex.Message);
this.Error = "Error when trying to update the excel: " + ex.Message + Environment.NewLine + "UpdateSQL=" + updateSQL;
}
finally
{
Conn.Close();
}
// then show a message if needed
if (dt.Rows.Count == 0)
{
//TODO: reporter
// Reporter.ToUser("No rows found in excel file matching criteria - " + sql);
// throw new Exception("No rows found in excel file matching criteria - " + sql);
}
}
}
public static void Inserir_Pedido(int id, string nome, string telefone, string endereço, string email)
{
string strConnection = "Data Source=.\\SQLEXPRESS;Initial Catalog=Loja;User ID=sa;Password=#lecoteco1975 ;Provider=SQLOLEDB";
//define a instrução SQL para somar Quantidade e agrupar resultados
String strSQL = "Inserir_Pedido";
//cria a conexão com o banco de dados
OleDbConnection dbConnection = new OleDbConnection(strConnection);
//cria a conexão com o banco de dados
OleDbConnection con = new OleDbConnection(strConnection);
//cria o objeto command para executar a instruçao sql
OleDbCommand cmd = new OleDbCommand(strSQL, con);
nome = nome.Replace("-", "");
nome = nome.Replace("%", "");
nome = nome.Replace("(", "");
nome = nome.Replace(")", "");
nome = nome.Replace("/", "");
nome = nome.Replace("\\", "");
nome = nome.Replace("&", "");
nome = nome.Replace("=", "");
nome = nome.Replace("@", "");
nome = nome.Replace("{", "");
nome = nome.Replace("}", "");
nome = nome.Replace("+", "");
nome = nome.Replace("|", "");
endereço = endereço.Replace("-", "");
endereço = endereço.Replace("%", "");
endereço = endereço.Replace("(", "");
endereço = endereço.Replace(")", "");
endereço = endereço.Replace("/", "");
endereço = endereço.Replace("\\", "");
endereço = endereço.Replace("&", "");
endereço = endereço.Replace("=", "");
endereço = endereço.Replace("@", "");
endereço = endereço.Replace("{", "");
endereço = endereço.Replace("}", "");
endereço = endereço.Replace("+", "");
endereço = endereço.Replace("|", "");
email = email.Replace("-", "");
email = email.Replace("%", "");
email = email.Replace("(", "");
email = email.Replace(")", "");
email = email.Replace("/", "");
email = email.Replace("\\", "");
email = email.Replace("&", "");
email = email.Replace("=", "");
email = email.Replace("@", "");
email = email.Replace("{", "");
email = email.Replace("}", "");
email = email.Replace("+", "");
email = email.Replace("|", "");
//abre a conexao
con.Open();
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@id", id);
cmd.Parameters.AddWithValue("@Nome", nome);
cmd.Parameters.AddWithValue("@Telefone", telefone);
cmd.Parameters.AddWithValue("@Endereço", endereço);
cmd.Parameters.AddWithValue("@Email", email);
//cria um dataadapter
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
//cria um objeto datatable
DataTable clientes = new DataTable();
//preenche o datatable via dataadapter
da.Fill(clientes);
con.Dispose();
con.Close();
cmd.Dispose();
dbConnection.Dispose();
dbConnection.Close();
}
override internal void LoadAll()
{
try
{
string schema = "";
if (-1 == this.Procedure.Schema.IndexOf("."))
{
schema = this.Procedure.Schema + ".";
}
string select = "SET FMTONLY ON EXEC [" + this.Procedure.Database.Name + "]." + schema +
this.Procedure.Name + " ";
int paramCount = this.Procedure.Parameters.Count;
if (paramCount > 0)
{
IParameters parameters = this.Procedure.Parameters;
IParameter param = null;
int c = parameters.Count;
for (int i = 0; i < c; i++)
{
param = parameters[i];
if (param.Direction == ParamDirection.ReturnValue)
{
paramCount--;
}
}
}
for (int i = 0; i < paramCount; i++)
{
if (i > 0)
{
select += ",";
}
select += "null";
}
OleDbDataAdapter adapter = new OleDbDataAdapter(select, this.dbRoot.ConnectionString);
DataTable metaData = new DataTable();
adapter.Fill(metaData);
AdvantageResultColumn resultColumn = null;
int count = metaData.Columns.Count;
for (int i = 0; i < count; i++)
{
resultColumn = this.dbRoot.ClassFactory.CreateResultColumn() as Advantage.AdvantageResultColumn;
resultColumn.dbRoot = this.dbRoot;
resultColumn.ResultColumns = this;
resultColumn._column = metaData.Columns[i];
this._array.Add(resultColumn);
}
}
catch {}
}
// https://vscode.ru/prog-lessons/ms-access-i-c-sharp-rabotaem-s-bd.html
// Запись в БД
private void button4_Click(object sender, EventArgs e)
{
// Запись в таблицу Оборудование
string t1 = textBox3.Text; // ИнвН
string t2 = (comboBox1.SelectedIndex + 1).ToString(); // ид модель
string t3 = textBox4.Text; // серийный
string t4 = textBox2.Text; // дата
string t5 = textBox7.Text; // цена
string t6 = textBox6.Text; // дата списания
string t7 = (comboBox3.SelectedIndex + 1).ToString(); ; // ид поставщик
string t8 = (comboBox2.SelectedIndex + 1).ToString(); ; // ид тип
string query = "INSERT INTO [Оборудование] ([ИнвN], [Id_модель], [СерийныйN], [Дата], [Цена], [Дата списания], [id_поставщик], [id_тип]) " +
"VALUES ("+
"'" + t1+"',"+
t2+", '"+
t3 + "','" +
t4 + "', " +
t5 + ",'" +
t6 + "'," +
t7 + "," +
t8 +
")";
// создаем объект OleDbCommand для выполнения запроса к БД MS Access
OleDbCommand command = new OleDbCommand(query, connection);
// выполняем запрос к MS Access
command.ExecuteNonQuery();
MessageBox.Show("Паспорт успешно сохранен в БД");
// обновить таблицу на форме
//dataGridView1.Cl
string sql = "SELECT * FROM [Оборудование]";
adapter = new OleDbDataAdapter(sql, connection);
ds = new DataSet();
adapter.Fill(ds);
dataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
dataGridView1.AllowUserToAddRows = false;
dataGridView1.DataSource = ds.Tables[0];
label9.Text = DateTime.Now.ToString();
/*
// Получить код
query = "SELECT [Код_заказ] FROM [Заказы]WHERE [Дата_создания]= "+
"'" + t4 + "'";
//query = "SELECT * FROM [Заказы]";// WHERE [Дата_создания]= "+
//command = new OleDbCommand(query, connection);
command.CommandText = query;
object o = command.ExecuteScalar();
string kod_zak = o.ToString();
// Запись таблицы
//object o;
string v1, v2, v3, v4;
for (int i = 0; i < dataGridView1.Rows.Count-1; ++i)
{
//o = dataGridView2[j, i].Value;
v1 = kod_zak;
v2 = dataGridView1[1, i].Value.ToString();
v3 = dataGridView1[4, i].Value.ToString();
v4 = dataGridView1[6, i].Value.ToString();
query = "INSERT INTO [Комплекты] ([Код_заказа], [Код_материала], [Количество], [Стоимость]) " +
"VALUES (" +
v1 + "," +
v2 + "," +
v3 + "," +
v4 +
")";
command = new OleDbCommand(query, connection);
command.ExecuteNonQuery();
MessageBox.Show("Заказ успешно сохранен в БД");
};
*/
}
private void loadPreview()
{
using (WaitDialogForm form = new WaitDialogForm())
{
//determine what to load either excle or csv
if (this.radioGroup1.SelectedIndex == 0) //excle file
{
if (this.radioGroup2.SelectedIndex == 0) //open excel file 2003
{
//Connection for MS Excel 2003 .xls format
MyConnection = new OleDbConnection(String.Format("provider=Microsoft.Jet.OLEDB.4.0; Data Source='{0}';Extended Properties=Excel 8.0;", txtFiletoLoad.Text));
}
else
{
//Connection for .xslx 2007 format
MyConnection = new OleDbConnection(String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source='{0}';Extended Properties=Excel 12.0;", txtFiletoLoad.Text));
}
//Select your Excel file
//ExcelExport (1)
//[Sheet1$]
MyCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", MyConnection);
DtSet = new System.Data.DataSet();
//Bind all excel data in to data set
MyCommand.Fill(DtSet, "[Sheet1$]");
Dt = DtSet.Tables[0];
MyConnection.Close();
//Check datatable have records
//check if record id for paydirect or checque update
if (CheuqeOption.ToString() == "0001" && this.radioGroup1.SelectedIndex == 0)
{
//Dt = LoadCSV().Tables[0];
Dt.Columns.Add("Cheque_Status", typeof(System.String));
//Dt.Columns.Add("DateDiff", typeof(System.String));
//Dt.Columns.Add("DateValidateAgainst", typeof(System.DateTime));
Dt.Columns.Add("Cheque_ValueDate", typeof(System.String));
foreach (DataRow row in Dt.Rows)
{
#region
//split = row["payment_ref_num"].ToString().Split(new char[] { '|' });
//string[] splitDate = split[2].Split(new char[] { '-' });
//string[] splitTime = row["payment_log_date"].ToString().Split(new char[] { ' ' });
//DateTime dtPaymentDate = new DateTime(Convert.ToInt32(splitDate[2]), Convert.ToInt32(splitDate[1]), Convert.ToInt32(splitDate[0]));
//row["payment_log_date"] = dtPaymentDate.ToString("MM/dd/yyyy");
//if (splitDate.Count() > 2 && splitTime.Count() > 0)
//{
// //CultureInfo culture = new CultureInfo("en-US");
// dtPaymentDate = Convert.ToDateTime(String.Format("{0} {1} {2}", dtPaymentDate.ToString("dd-MM-yyyy"), splitTime[1], splitTime[2]));
// int dateDiff = dtPaymentDate.Subtract(dateTimePicker1.Value).Days;
// row["payment_log_date"] = dtPaymentDate.ToString("MM/dd/yyyy");
// row["DateDiff"] = Math.Abs(dateDiff);
// row["DateValidateAgainst"] = dateTimePicker1.Value;
//}
#endregion
if (row["payment_method_name"].ToString() == "Other Bank Cheque" || row["payment_method_name"].ToString() == "Other Bank Cheque/Dr")
{
//Own Bank Cheque
row["Cheque_Status"] = "Pending";
}
else
{
row["Cheque_Status"] = "Cleared";
row["Cheque_ValueDate"] = row["payment_log_date"];
}
}
Dt.AcceptChanges();
//label4.Text = Dt.Rows.Count + " Rows to be Imported ";
ReformatAmount2(Dt);
Dt.DefaultView.Sort = "revenue_code ASC";
gridControl1.DataSource = Dt;
gridView1.BestFitColumns();
gridView1.GroupFooterShowMode = DevExpress.XtraGrid.Views.Grid.GroupFooterShowMode.VisibleAlways;
//GridGroupSummaryItem item = new GridGroupSummaryItem();
//item.FieldName = "payment_ref_num";
//item.SummaryType = DevExpress.Data.SummaryItemType.Count;
//gridView1.GroupSummary.Add(item);
GridGroupSummaryItem item2 = new GridGroupSummaryItem()
{
FieldName = "Cheque_Status", SummaryType = DevExpress.Data.SummaryItemType.Count
};
gridView1.GroupSummary.Add(item2);
}
else
{
if (Dt.Rows.Count > 0)
{
RemoveColumn(Dt, "F1");
RemoveColumn(Dt, "F14");
RemoveColumn(Dt, "F15");
ReformatAmount(Dt);
gridControl1.DataSource = Dt.DefaultView;
gridView1.OptionsBehavior.Editable = false;
gridView1.BestFitColumns();
gridView1.Columns["Amount"].DisplayFormat.FormatType = FormatType.Numeric;
gridView1.Columns["Amount"].DisplayFormat.FormatString = "n2";
}
}
//if (Dt.Rows.Count > 0)
//{
// //RemoveColumn(Dt, "F1");
// //ReformatAmount(Dt);
// gridControl1.DataSource = Dt.DefaultView;
// gridView1.OptionsBehavior.Editable = false;
// gridView1.BestFitColumns();
// //gridView1.Columns["Amount"].DisplayFormat.FormatType = FormatType.Numeric;
// //gridView1.Columns["Amount"].DisplayFormat.FormatString = "n2";
//}
//Dt.AcceptChanges();
label4.Text = Dt.Rows.Count + " Rows of Records to be Imported ";
}
else//csv file
{
try
{
// select format, encoding, an write the schema file
Format();
Encoding();
writeSchema();
Dt = LoadCSV().Tables[0];
Dt.Columns.Add("Cheque_Status", typeof(System.String));
//Dt.Columns.Add("DateDiff", typeof(System.String));
//Dt.Columns.Add("DateValidateAgainst", typeof(System.DateTime));
Dt.Columns.Add("Cheque_ValueDate", typeof(System.String));
foreach (DataRow row in Dt.Rows)
{
//split = row["payment_ref_num"].ToString().Split(new char[] { '|' });
//string[] splitDate = split[2].ToString().Split(new char[] { '-' });
//string[] splitTime = row["payment_log_date"].ToString().Split(new char[] { ' ' });
//DateTime dtPaymentDate = new DateTime(Convert.ToInt32(splitDate[2]), Convert.ToInt32(splitDate[1]), Convert.ToInt32(splitDate[0]));
//row["payment_log_date"] = dtPaymentDate.ToString("MM/dd/yyyy");
//if (splitDate.Count() > 2 && splitTime.Count() > 0)
//{
// //CultureInfo culture = new CultureInfo("en-US");
// dtPaymentDate = Convert.ToDateTime(String.Format("{0} {1} {2}", dtPaymentDate.ToString("dd-MM-yyyy"), splitTime[1], splitTime[2]));
// int dateDiff = dtPaymentDate.Subtract(dateTimePicker1.Value).Days;
// row["payment_log_date"] = dtPaymentDate.ToString("MM/dd/yyyy");
// row["DateDiff"] = Math.Abs(dateDiff);
// row["DateValidateAgainst"] = dateTimePicker1.Value;
//}
if (row["payment_method_name"].ToString() == "Other Bank Cheque" || row["payment_method_name"].ToString() == "Other Bank Cheque/Dr")
{
//Own Bank Cheque
row["Cheque_Status"] = "Pending";
}
else
{
row["Cheque_Status"] = "Cleared";
row["Cheque_ValueDate"] = row["payment_log_date"];
}
}
Dt.AcceptChanges();
label4.Text = Dt.Rows.Count + " Rows to be Imported ";
Dt.DefaultView.Sort = "revenue_code ASC";
gridControl1.DataSource = Dt;
gridView1.BestFitColumns();
gridView1.GroupFooterShowMode = DevExpress.XtraGrid.Views.Grid.GroupFooterShowMode.VisibleAlways;
//GridGroupSummaryItem item = new GridGroupSummaryItem();
//item.FieldName = "payment_ref_num";
//item.SummaryType = DevExpress.Data.SummaryItemType.Count;
//gridView1.GroupSummary.Add(item);
GridGroupSummaryItem item2 = new GridGroupSummaryItem()
{
FieldName = "Cheque_Status", SummaryType = DevExpress.Data.SummaryItemType.Count
};
gridView1.GroupSummary.Add(item2);
}
catch (Exception e)
{
MessageBox.Show(e.Message, "Error - loadPreview", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
if (Dt.Rows.Count > 0)
{
bttnPreview.Enabled = false;
bttnImport.Enabled = true;
bttnBrowse.Enabled = false;
}
else
{
bttnPreview.Enabled = true;
bttnImport.Enabled = false;
bttnBrowse.Enabled = false;
}
}
}
/// <summary>
/// Provide the required columns data from Excel
/// </summary>
/// <param name="fileName">Full path of the excel of format .xlsx</param>
/// <param name="sheetName">Name of the sheet fo which you want to read the data in Excel</param>
/// <param name="colKey">Column1, which you want to read from Excel, It will be the key for output data</param>
/// <param name="colValue">Column2, which you want to read from Excel, It will be the value for output data</param>
/// <returns>Dictionary with key as #<paramref name="colKey"/> and value as #<paramref name="colValue"/></returns>
public static Dictionary <string, string> ReadExcel(string fileName, string sheetName, string colKey, string colValue)
{
string conString = string.Empty;
DataTable dtexcel = new DataTable();
if (fileName.Substring(fileName.LastIndexOf(".")).CompareTo(".xlsx") != 0)
{
// conString = "provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + ";Extended Properties='Excel 8.0;HRD=Yes;IMEX=1';"; //for below excel 2007
Console.WriteLine("Expected format is .xlsx");
return(null);
}
else
{
conString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties='Excel 12.0;HRD=Yes';"; //for above excel 2007
}
using (OleDbConnection Connection = new OleDbConnection(conString))
{
try
{
Connection.Open();
dtexcel = Connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
//If you apply below commented region below lines should go down..
var query = String.Format("select * from [{0}]", sheetName + "$");
OleDbDataAdapter oleAdpt = new OleDbDataAdapter(query, Connection); //here we read data from sheet1
oleAdpt.Fill(dtexcel); //fill excel data into dataTable
}
catch (Exception ex)
{
Console.WriteLine("Exception occurred ", ex);
return(null);
}
finally
{
Connection.Close();
}
}
Dictionary <string, string> dataMap = new Dictionary <string, string>();
foreach (DataRow dtRow in dtexcel.Rows)
{
dataMap.Add(dtRow[colKey].ToString(), dtRow[colValue].ToString());
}
return(dataMap);
#region To read more Sheets in single excel..
//var itemsOfWorksheet = new List<SelectListItem>();
//if (dtexcel != null)
//{
// //validate worksheet name.
// string worksheetName;
// for (int cnt = 0; cnt < dtexcel.Rows.Count; cnt++)
// {
// worksheetName = dtexcel.Rows[cnt]["TABLE_NAME"].ToString();
// if (worksheetName.Contains('\''))
// {
// worksheetName = worksheetName.Replace('\'', ' ').Trim();
// }
// if (worksheetName.Trim().EndsWith("$"))
// itemsOfWorksheet.Add(new SelectListItem { Text = worksheetName.TrimEnd('$'), Value = worksheetName });
// }
//}
#endregion
}
public static void UpdateExcel(string originalFilePath)
{
DataSet ds = new DataSet();
string connectionString = GetConnectionString(originalFilePath);
using (OleDbConnection conn = new OleDbConnection(connectionString))
{
conn.Open();
OleDbCommand cmd = new OleDbCommand();
cmd.Connection = conn;
// Get all Sheets in Excel File
DataTable dtSheet = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
// Loop through all Sheets to get data
foreach (DataRow dr in dtSheet.Rows)
{
string sheetName = dr["TABLE_NAME"].ToString();
if (!sheetName.EndsWith("$"))
{
continue;
}
// Get all rows from the Sheet
cmd.CommandText = "SELECT * FROM [" + sheetName + "]";
DataTable dt = new DataTable();
dt.TableName = sheetName;
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
da.Fill(dt);
//here table has data now,so we do it
//列项
foreach (DataRow row in dt.Rows)
{
var regCode = row["注册代码"].ToString();
var startTime = row["故障发生时间"].ToString();
var endTime = row["故障恢复时间"].ToString();
var etor_bianhao = DbHelper.GetCannyEtorBianhaoByRegCodeFromLocalDb(regCode);
var ret = DbHelper.GetDtuStatusFromCannyDbByDtuBianhao(etor_bianhao, startTime);
if (ret != null)
{
var isTrap = ret.Item1;
var solution = ret.Item2;
//var regCodeInt = int.Parse(regCode.Substring(14));
//string trap = (0 == regCodeInt % 2) ? "Y" : "N";
cmd.CommandText = $"Update [{sheetName}] set 是否困人 = '{isTrap}',处置信息='{solution}' where 注册代码='{regCode}'";
cmd.ExecuteNonQuery();
}
}
ds.Tables.Add(dt);
}
cmd = null;
conn.Close();
}
}
private void Form1_Load(object sender, EventArgs e)
{
using (OleDbConnection conn = new OleDbConnection(connStr))
{
string sql = "Select b.姓名,s.成绩 from [学生成绩表] s left join [基本情况] b on b.学号 = s.学号";
DataTable dt = new DataTable();
OleDbDataAdapter da = new OleDbDataAdapter(sql, conn);
da.Fill(dt);
//dataGridView1.DataSource = dt;
var lstRawRows = dt.Rows.OfType <DataRow>()
.Select(r => new RawRow(r[0].ToString(), Convert.ToInt32(r[1])));
var lstStudents = lstRawRows.GroupBy(x => x.Name)
.Select(g => new Student(
g.Key,
g.Average(x => x.score),
g.Min(x => x.score))).ToList();
dataGridView1.DataSource = lstStudents;
string[] xval = null;
double[] yval = null;
int[] yval1 = null;
//加时间限制，统计当天，下同
//.Where(x=>Convert.ToDateTime(x["st"]).Date<=DateTime.Now.Date
//&& Convert.ToDateTime(x["et"]).Date >= DateTime.Now.Date);
xval = lstStudents
.Select(x => x.Name)
.Distinct().ToArray();
yval = lstStudents
.Select(x => x.Ave)
.ToArray();
yval1 = lstStudents
.Select(x => x.Min)
.ToArray();
Chart chart = new Chart();
panel1.Controls.Add(chart);
chart.Dock = DockStyle.Fill;
chart.ChartAreas.Add(new ChartArea());
chart.Series.Add(new Series("Ave"));
chart.Series["Ave"].ChartType = SeriesChartType.Column;
chart.Series["Ave"]["PieLabelStyle"] = "Outside";
chart.Series["Ave"]["PieLineColor"] = "Black";
chart.Series["Ave"].IsValueShownAsLabel = true;
chart.Series["Ave"].Points.DataBindXY(xval, yval);
chart.Series["Ave"].LegendText = "平均成绩";
chart.Series.Add(new Series("Min"));
chart.Series["Min"].ChartType = SeriesChartType.Column;
chart.Series["Min"]["PieLabelStyle"] = "Outside";
chart.Series["Min"]["PieLineColor"] = "Black";
chart.Series["Min"].IsValueShownAsLabel = true;
chart.Series["Min"].Points.DataBindXY(xval, yval1);
chart.Series["Min"].LegendText = "最低成绩";
Legend leg = new Legend();
leg.Docking = Docking.Right;
chart.Legends.Add(leg);
}
// get records from the table
}
private void Connect_Read_Raw(string strConnection)
{
// Create and open the connection in a using block. This ensures that all resources
// will be closed and disposed when the code exits.
Console.WriteLine("(raw)");
using (OleDbConnection connection = new OleDbConnection(strConnection))
{
// Open the connection in a try/catch block
try
{
if (m_eDbReadTechnology == DatabaseReadTechnology.eRbRead_DataReader)
{
// Using System.Data.OleDb.OleDbDataReader : IDataReader
Console.WriteLine("(OleDb.OleDbDataReader)");
// Create and open the connection in a using block. This ensures that all resources
// will be closed and disposed when the code exits.
// Note:
// * "DbConnection" is the base class for OleDbConnection, OdbcConnection
// * "DbCommand" is the base class for OleDbCommand, OdbcCommand
// * "DbParameterCollection" is the base class for OleDbParameterCollection, OdbcParameterCollection
// * "DbDataReader" is the base class for OleDbDataReader, OdbcDataReader
// We could write a generic function to access the database with OleDB or ODBC, but it
// is simpler to write an unique method for each technology.
// Create and execute the DataReader, writing the result to the console window
int recordsRead = 0;
Console.WriteLine("\t{0}{1}{2}",
Northwind_Products.colProductID.PadRight(Northwind_Products.colProductIDWidth),
Northwind_Products.colUnitPrice.PadRight(Northwind_Products.colUnitPriceWidth),
Northwind_Products.colProductName);
connection.Open();
OleDbCommand command = new OleDbCommand(m_cfgDatabase.querySELECT, connection);
command.Parameters.AddWithValue("@pricePoint", m_cfgDatabase.paramValue);
// This also works: command.Parameters.AddWithValue(string.Empty, paramValue);
OleDbDataReader reader = command.ExecuteReader();
while (reader.Read())
{
recordsRead++;
Console.WriteLine("\t{0}{1}{2}",
((int)reader[Northwind_Products.colProductID]).ToString().PadRight(Northwind_Products.colProductIDWidth),
((decimal)reader[Northwind_Products.colUnitPrice]).ToString("0.00").PadRight(Northwind_Products.colUnitPriceWidth),
(string)reader[Northwind_Products.colProductName]);
}
reader.Close();
Console.WriteLine(" ({0} records)", recordsRead);
}
else if (m_eDbReadTechnology == DatabaseReadTechnology.eRbRead_DataAdapter)
{
// This version uses:
// * System.Data.DataSet
// * System.Data.OleDb.OleDbDataAdapter : IDbDataAdapter
// * System.Data.DataRow
Console.WriteLine("(DataSet, OleDb.OleDbDataAdapter and DataRow)");
// Create and fill the DataSet, writing the result to the console window
int recordsRead = 0;
Console.WriteLine("\t{0}{1}{2}",
Northwind_Products.colProductID.PadRight(Northwind_Products.colProductIDWidth),
Northwind_Products.colUnitPrice.PadRight(Northwind_Products.colUnitPriceWidth),
Northwind_Products.colProductName);
connection.Open();
DataSet ds = new DataSet();
OleDbDataAdapter adapter = new OleDbDataAdapter(m_cfgDatabase.querySELECT, connection);
adapter.SelectCommand.Parameters.Add("@pricePoint", OleDbType.Integer).Value = m_cfgDatabase.paramValue;
adapter.Fill(ds);
foreach (DataRow row in ds.Tables[0].Rows)
{
recordsRead++;
Console.WriteLine("\t{0}{1}{2}",
((int)row[Northwind_Products.colProductID]).ToString().PadRight(Northwind_Products.colProductIDWidth),
((decimal)row[Northwind_Products.colUnitPrice]).ToString("0.00").PadRight(Northwind_Products.colUnitPriceWidth),
(string)row[Northwind_Products.colProductName]);
}
Console.WriteLine(" ({0} records)", recordsRead);
}
}
catch (Exception ex)
{
Console.WriteLine(UtilitiesGeneral.FormatException(
this.ToString(), System.Reflection.MethodBase.GetCurrentMethod().Name, ex.Message));
}
}
Console.WriteLine();
}
public static DataTable ImportExcel(String filePath)
{
string Excel03ConString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1}'";
string Excel07ConString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1}'";
if (filePath != null && !filePath.Equals(""))
{
string extension = System.IO.Path.GetExtension(filePath);
string header = "YES";
string connectionString, sheetName;
connectionString = string.Empty;
switch (extension)
{
case ".xls": //Excel 97-03
connectionString = string.Format(Excel03ConString, filePath, header);
break;
case ".xlsx": //Excel 07
connectionString = string.Format(Excel07ConString, filePath, header);
break;
default:
{
MessageBoxEX.DialogMessageBox("Thông báo", "File chọn không hợp lệ. Vui lòng chọn file Excel", "Bạn hãy kiểm tra lại file chọn cho phù hợp");
return(null);
}
}
//Get the name of the First Sheet.
using (OleDbConnection con = new OleDbConnection(connectionString))
{
using (OleDbCommand cmd = new OleDbCommand())
{
cmd.Connection = con;
con.Open();
DataTable dtExcelSchema = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
sheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
con.Close();
}
}
using (OleDbConnection con = new OleDbConnection(connectionString))
{
using (OleDbCommand cmd = new OleDbCommand())
{
using (OleDbDataAdapter oda = new OleDbDataAdapter())
{
DataTable dt = new DataTable();
cmd.CommandText = "SELECT * From [" + sheetName + "]";
cmd.Connection = con;
con.Open();
oda.SelectCommand = cmd;
oda.Fill(dt);
con.Close();
return(dt);
}
}
}
}
else
{
MessageBoxEX.DialogMessageBox("Thông báo", " Vui lòng chọn file Excel", "Bạn hãy kiểm tra lại file chọn cho phù hợp");
return(null);
}
}
private void LufkinBooks_Load(object sender, EventArgs e)
{
#region Connect to DB
try
{
connection = new OleDbConnection();
connection.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=..\\..\\LufkinLib.V2.accdb; Persist Security Info = False;";
}
catch (Exception ex)
{
MessageBox.Show(String.Format("Error: Failed to create a database connection. \n{0}", ex.Message));
return;
}
#endregion
#region Load Books for Staff
if (LufkinBooks.ViewBooks == "Staff")
{
try
{
//Set the command text to query DB with
command.CommandText = "SELECT * FROM Media";
dataSet = new DataSet();
connection.Open();
command.Connection = connection;
adapter = new OleDbDataAdapter(command);
adapter.Fill(dataSet, "Media");
rowCollection = dataSet.Tables["Media"].Rows;
table = dataSet.Tables["Media"];
int count = table.Rows.Count;
if (count >= 1)
{
dgvBooks.DataSource = dataSet.Tables["Media"];
}
else
{
MessageBox.Show("No books found!");
}
connection.Close();
}
catch (Exception ex)
{
MessageBox.Show(String.Format("Error: Failed to retrieve the required data from the DataBase.\n{0}", ex));
return;
}
finally
{
if (connection != null)
{
connection.Close();
}
}
}
#endregion
#region Load Books for Member
if (LufkinBooks.ViewBooks == "Member")
{
try
{
//Set the command text to query DB with
command.CommandText = "SELECT * FROM Media";
dataSet = new DataSet();
connection.Open();
command.Connection = connection;
adapter = new OleDbDataAdapter(command);
adapter.Fill(dataSet, "Media");
rowCollection = dataSet.Tables["Media"].Rows;
table = dataSet.Tables["Media"];
int count = table.Rows.Count;
if (count >= 1)
{
dgvBooks.DataSource = dataSet.Tables["Media"];
dgvBooks.Columns.Remove("ID");
dgvBooks.Columns.Remove("CheckedOutBy");
dgvBooks.Columns.Remove("CheckOutDate");
dgvBooks.Columns.Remove("CheckedInBy");
dgvBooks.Columns.Remove("CheckInDate");
dgvBooks.Columns.Remove("InPossesionOf");
dgvBooks.Columns.Remove("NextForBook");
}
else
{
MessageBox.Show("No books found!");
}
connection.Close();
}
catch (Exception ex)
{
MessageBox.Show(String.Format("Error: Failed to retrieve the required data from the DataBase.\n{0}", ex));
return;
}
finally
{
if (connection != null)
{
connection.Close();
}
}
}
#endregion
#region Load Books for Anyone
if (LufkinBooks.ViewBooks == "Anyone")
{
try
{
//Set the command text to query DB with
command.CommandText = "SELECT * FROM Media";
dataSet = new DataSet();
connection.Open();
command.Connection = connection;
adapter = new OleDbDataAdapter(command);
adapter.Fill(dataSet, "Media");
rowCollection = dataSet.Tables["Media"].Rows;
table = dataSet.Tables["Media"];
int count = table.Rows.Count;
if (count >= 1)
{
dgvBooks.DataSource = dataSet.Tables["Media"];
dgvBooks.Columns.Remove("ID");
dgvBooks.Columns.Remove("ISBN");
dgvBooks.Columns.Remove("Section");
dgvBooks.Columns.Remove("Unavailable");
dgvBooks.Columns.Remove("CheckedOutBy");
dgvBooks.Columns.Remove("CheckOutDate");
dgvBooks.Columns.Remove("CheckedInBy");
dgvBooks.Columns.Remove("CheckInDate");
dgvBooks.Columns.Remove("InPossesionOf");
dgvBooks.Columns.Remove("NextForBook");
}
else
{
MessageBox.Show("No books found!");
}
connection.Close();
}
catch (Exception ex)
{
MessageBox.Show(String.Format("Error: Failed to retrieve the required data from the DataBase.\n{0}", ex));
return;
}
finally
{
if (connection != null)
{
connection.Close();
}
}
}
#endregion
}
private void button2_Click(object sender, EventArgs e)
{
SaveFileDialog sfd = new SaveFileDialog();
sfd.Filter = "Excel | *.xls";
if (sfd.ShowDialog() == DialogResult.OK)
{
string fileName = sfd.FileName;
Microsoft.Office.Interop.Excel.Workbook workBook = Main.ExcelApp.Workbooks.Add(); // 워크북 추가
Microsoft.Office.Interop.Excel.Worksheet workSheet;
for (int i = 0; i < lsvPay.Items.Count; i++)
{
string name = lsvPay.Items[i].SubItems[0].Text;
if (workBook.Worksheets.Count <= i)
{
workBook.Worksheets.Add();
}
workSheet = workBook.Worksheets.get_Item(1) as Microsoft.Office.Interop.Excel.Worksheet;
workSheet.Name = name;
string query = "SELECT contract.ID, contract.inputdate, contract.recevicedocument, contract.shopname, contract.type, contract.registrationnumber, contract.phonenumber, contract.address, contract.representative, contract.salerpersion, contract.storeid, contract.offerdb, contract.dbmanager, contract.iscontract, contract.content FROM contract, paytable where contract.storeid = paytable.storeid and paytable.avablecalc = 'O' and contract.salerpersion = '" + name + "'";
DataSet ds = new DataSet();
OleDbDataAdapter adp = new OleDbDataAdapter(query, Main.conn);
adp.Fill(ds);
workSheet.Cells[1, 1] = lsvPay.Columns[0].Text;
workSheet.Cells[1, 2] = lsvPay.Columns[1].Text;
workSheet.Cells[1, 3] = lsvPay.Columns[2].Text;
workSheet.Cells[1, 4] = lsvPay.Columns[3].Text;
workSheet.Cells[2, 1] = lsvPay.Items[i].SubItems[0].Text;
workSheet.Cells[2, 2] = lsvPay.Items[i].SubItems[1].Text;
workSheet.Cells[2, 3] = lsvPay.Items[i].SubItems[2].Text;
workSheet.Cells[2, 4] = lsvPay.Items[i].SubItems[3].Text;
int colIndex = 1;
foreach (ColumnHeader col in lsvPayList.Columns)
{
workSheet.Cells[3, colIndex] = col.Text;
colIndex++;
}
int rowIndex = 4;
foreach (DataRow row in ds.Tables[0].Rows)
{
colIndex = 1;
foreach (object colItem in row.ItemArray)
{
string str = colItem.ToString();
workSheet.Cells[rowIndex, colIndex] = str;
colIndex++;
}
rowIndex++;
}
}
workBook.SaveAs(fileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal);
workBook.Close(true);
MessageBox.Show("저장 완료");
}
}
private void TreeView1_NodeMouseClick_1(object sender, TreeNodeMouseClickEventArgs e)
{
name = e.Node.Text.ToString();
if (name == "运行时分")
{
String ConStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Application.StartupPath + "\\行车记录.mdb";
OleDbConnection conn = new OleDbConnection(ConStr);
conn.Open();
DataTable dtt = new DataTable();
OleDbDataAdapter ada = new OleDbDataAdapter("select*from 京沪高铁列车时刻表190317 ", conn);
ada.Fill(dtt); //以上连接数据库
dataGridView2.DataSource = dtt; //将数据库内容储存，记录行数（列车数）
}
if (name == "车站布置")
{
String ConStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Application.StartupPath + "\\行车记录.mdb";
OleDbConnection conn = new OleDbConnection(ConStr);
conn.Open();
DataTable dtt = new DataTable();
OleDbDataAdapter ada = new OleDbDataAdapter("select*from 车站 ", conn);
ada.Fill(dtt); //以上连接数据库
dataGridView2.DataSource = dtt; //将数据库内容储存，记录行数（列车数）
}
if (name == "道岔设置")
{
String ConStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Application.StartupPath + "\\行车记录.mdb";
OleDbConnection conn = new OleDbConnection(ConStr);
conn.Open();
DataTable dtt = new DataTable();
OleDbDataAdapter ada = new OleDbDataAdapter("select*from 道岔标准2", conn);
ada.Fill(dtt); //以上连接数据库
dataGridView2.DataSource = dtt; //将数据库内容储存，记录行数（列车数）
}
if (name == "动车组要素")
{
String ConStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Application.StartupPath + "\\行车记录.mdb";
OleDbConnection conn = new OleDbConnection(ConStr);
conn.Open();
DataTable dtt = new DataTable();
OleDbDataAdapter ada = new OleDbDataAdapter("select*from 动车组", conn);
ada.Fill(dtt); //以上连接数据库
dataGridView2.DataSource = dtt; //将数据库内容储存，记录行数（列车数）
}
if (name == "运用模式")
{
System.Diagnostics.Process.Start(Application.StartupPath + "\\中国动车组交路查询.exe").WaitForExit();
}
if (name == "区间")
{
String ConStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Application.StartupPath + "\\行车记录.mdb";
OleDbConnection conn = new OleDbConnection(ConStr);
conn.Open();
DataTable dtt = new DataTable();
OleDbDataAdapter ada = new OleDbDataAdapter("select*from 京沪高铁线路区间数据", conn);
ada.Fill(dtt); //以上连接数据库
dataGridView2.DataSource = dtt; //将数据库内容储存，记录行数（列车数）
}
/* if (name == "联锁方式")
* {
* String ConStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Application.StartupPath + "\\行车记录.mdb";
* OleDbConnection conn = new OleDbConnection(ConStr);
* conn.Open();
* DataTable dtt = new DataTable();
*
* OleDbDataAdapter ada = new OleDbDataAdapter("select*from 固定设备-轨道电路", conn);
* ada.Fill(dtt); //以上连接数据库
* dataGridView2.DataSource = dtt; //将数据库内容储存，记录行数（列车数）
* }
* if (name == "信号制式")
* if (name == "信号制式")
* {
* String ConStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Application.StartupPath + "\\行车记录.mdb";
* OleDbConnection conn = new OleDbConnection(ConStr);
* conn.Open();
* DataTable dtt = new DataTable();
*
* OleDbDataAdapter ada = new OleDbDataAdapter("select*from 固定设备-信号机", conn);
* ada.Fill(dtt); //以上连接数据库
* dataGridView2.DataSource = dtt; //将数据库内容储存，记录行数（列车数）
* }*/
if (name == "设备参数")
{
String ConStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Application.StartupPath + "\\行车记录.mdb";
OleDbConnection conn = new OleDbConnection(ConStr);
conn.Open();
DataTable dtt = new DataTable();
OleDbDataAdapter ada = new OleDbDataAdapter("select*from 道岔标准", conn);
ada.Fill(dtt); //以上连接数据库
dataGridView2.DataSource = dtt; //将数据库内容储存，记录行数（列车数）
}
if (name == "分界点")
{
String ConStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Application.StartupPath + "\\行车记录.mdb";
OleDbConnection conn = new OleDbConnection(ConStr);
conn.Open();
DataTable dtt = new DataTable();
OleDbDataAdapter ada = new OleDbDataAdapter("select*from 线路2", conn);
ada.Fill(dtt); //以上连接数据库
dataGridView2.DataSource = dtt; //将数据库内容储存，记录行数（列车数）
}
}
//Populates the form, given a round and group. Assumes isValidGroup(round, group) == true
private void populateData(int round, int group, int nextround, int nextgroup)
{
//populate curr and next
labelCurrRound.Text = (round + 1).ToString();
labelCurrGroup.Text = (group + 1).ToString();
textBoxNextRound.Text = (nextround + 1).ToString();
textBoxNextGroup.Text = (nextgroup + 1).ToString();
//populate Player names
GroupOfFour g = theEvent.lstRounds[round].lstGroups[group];
labelAPlayer.Text = g.playerA.displayName.PadRight(21).Substring(0, 21);
labelBPlayer.Text = g.playerB.displayName.PadRight(21).Substring(0, 21);
labelCPlayer.Text = g.playerC.displayName.PadRight(21).Substring(0, 21);
labelDPlayer.Text = g.playerD.displayName.PadRight(21).Substring(0, 21);
//Get scores from db for current round/group and populate scores/subs if they exist. Else populate with zeroes.
if (!doScoresExistFor(theEvent.nID, (round + 1), (group + 1)))
{
textBoxAPutts.Text = "0";
textBoxBPutts.Text = "0";
textBoxCPutts.Text = "0";
textBoxDPutts.Text = "0";
textBoxAScore.Text = "0";
textBoxBScore.Text = "0";
textBoxCScore.Text = "0";
textBoxDScore.Text = "0";
checkBoxASub.Checked = false;
checkBoxBSub.Checked = false;
checkBoxCSub.Checked = false;
checkBoxDSub.Checked = false;
}
else
{
string strCmd = "SELECT * FROM Scores WHERE eventID = " + theEvent.nID;
strCmd += " AND roundNumber = " + (round + 1) + " AND groupNumber = " + (group + 1);
strCmd += " ORDER BY rowID ASC";
OleDbCommand dbCmd = new OleDbCommand(strCmd, Globals.g_dbConnection);
OleDbDataAdapter dbAdapter = new OleDbDataAdapter(dbCmd);
DataSet dataSet = new DataSet();
try
{
dbAdapter.Fill(dataSet, "Scores");
}
catch (Exception ex)
{
MessageBox.Show("ERROR 1013: Could not write to database.\n" + ex.Message);
return;
}
int nRow = 0;
foreach (DataRow r in dataSet.Tables["Scores"].Rows)
{
if (nRow == 0) //0th element
{
textBoxAPutts.Text = r["puttScore"].ToString();
textBoxAScore.Text = r["pointScore"].ToString();
checkBoxASub.Checked = Convert.ToBoolean(r["isSubstitution"].ToString());
}
else if (nRow == 1)
{
textBoxBScore.Text = r["pointScore"].ToString();
textBoxBPutts.Text = r["puttScore"].ToString();
checkBoxBSub.Checked = Convert.ToBoolean(r["isSubstitution"].ToString());
}
else if (nRow == 2)
{
textBoxCPutts.Text = r["puttScore"].ToString();
textBoxCScore.Text = r["pointScore"].ToString();
checkBoxCSub.Checked = Convert.ToBoolean(r["isSubstitution"].ToString());
}
else if (nRow == 3)
{
textBoxDPutts.Text = r["puttScore"].ToString();
textBoxDScore.Text = r["pointScore"].ToString();
checkBoxDSub.Checked = Convert.ToBoolean(r["isSubstitution"].ToString());
}
nRow++;
}
}
}
public ICollection <ErrorHandler> BulkInsert(string argExcelPath, string argQuery, string strTableName, string argFileExt, string argUserName, string argClientCode)
{
DataTable dtExcel = null;
ValuationClass ObjValuationClass = null;
string xConnStr = "";
string strSheetName = "";
DataSet dsExcel = new DataSet();
DataTable dtTableSchema = new DataTable();
OleDbConnection objXConn = null;
OleDbDataAdapter objDataAdapter = new OleDbDataAdapter();
List <ErrorHandler> lstErr = new List <ErrorHandler>();
if (argFileExt.ToString() == ".xls")
{
xConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + argExcelPath.Trim() + ";" +
"Extended Properties=Excel 8.0";
}
else
{
xConnStr = "Provider=Microsoft.ACE.OLEDB.12.0;" +
"Data Source=" + argExcelPath.Trim() + ";" +
"Extended Properties=Excel 12.0";
}
try
{
objXConn = new OleDbConnection(xConnStr);
objXConn.Open();
dtTableSchema = objXConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
if (argFileExt.ToString() == ".xls")
{
strSheetName = Convert.ToString(dtTableSchema.Rows[0]["TABLE_NAME"]);
}
else
{
strSheetName = Convert.ToString(dtTableSchema.Rows[0]["TABLE_NAME"]);
if (strSheetName.IndexOf(@"_xlnm#_FilterDatabase") >= 0)
{
strSheetName = Convert.ToString(dtTableSchema.Rows[1]["TABLE_NAME"]);
}
}
argQuery = argQuery + " [" + strSheetName + "]";
OleDbCommand objCommand = new OleDbCommand(argQuery, objXConn);
objDataAdapter.SelectCommand = objCommand;
objDataAdapter.Fill(dsExcel);
dtExcel = dsExcel.Tables[0];
/*****************************************/
DataAccess da = new DataAccess();
da.Open_Connection();
da.BEGIN_TRANSACTION();
try
{
foreach (DataRow drExcel in dtExcel.Rows)
{
ObjValuationClass = new ValuationClass();
ObjValuationClass.ValClassType = Convert.ToString(drExcel["ValClassType"]).Trim();
ObjValuationClass.ValClassDesc = Convert.ToString(drExcel["ValClassDesc"]).Trim();
ObjValuationClass.ItemCategoryCode = Convert.ToString(drExcel["ItemCategoryCode"]).Trim();
ObjValuationClass.MaterialTypeCode = Convert.ToString(drExcel["MaterialTypeCode"]).Trim();
ObjValuationClass.CreatedBy = Convert.ToString(argUserName);
ObjValuationClass.ModifiedBy = Convert.ToString(argUserName);
ObjValuationClass.ClientCode = Convert.ToString(argClientCode);
SaveValuationClass(ObjValuationClass, da, lstErr);
foreach (ErrorHandler objerr in lstErr)
{
if (objerr.Type == "E")
{
da.ROLLBACK_TRANSACTION();
break;
}
}
}
da.COMMIT_TRANSACTION();
}
catch (Exception ex)
{
if (da != null)
{
da.ROLLBACK_TRANSACTION();
}
objErrorHandler.Type = ErrorConstant.strAboartType;
objErrorHandler.MsgId = 0;
objErrorHandler.Module = ErrorConstant.strInsertModule;
objErrorHandler.ModulePart = ErrorConstant.strMasterModule;
objErrorHandler.Message = ex.Message.ToString();
objErrorHandler.RowNo = 0;
objErrorHandler.FieldName = "";
objErrorHandler.LogCode = "";
lstErr.Add(objErrorHandler);
}
finally
{
if (da != null)
{
da.Close_Connection();
da = null;
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
objXConn.Close();
}
return(lstErr);
}
public Dictionary <Category, List <Player> > ReadPlayerList(string path, ExcelType extension)
{
Connect(path, extension);
OleDbDataAdapter objDA = new OleDbDataAdapter("select * from [Sheet1$]", _connection);
DataSet excelDataSet = new DataSet();
objDA.Fill(excelDataSet);
Disconnect();
var result = excelDataSet.Tables[0];
var goldPlayers = new Dictionary <string, Player>();
var silverPlayers = new Dictionary <string, Player>();
var bronzeAPlayers = new Dictionary <string, Player>();
var bronzeBPlayers = new Dictionary <string, Player>();
foreach (DataRow row in result.Rows)
{
for (int i = 0; i < result.Columns.Count; i++)
{
var column = result.Columns[i];
var columnName = column.ToString();
var player = new Player();
var cellValue = row[result.Columns[i]].ToString();
if (cellValue != "")
{
switch (columnName)
{
case "Gold":
player.Category = Category.Gold;
player.FullName = cellValue;
player.Points = Convert.ToInt32(row[result.Columns[i + 1]].ToString());
goldPlayers.Add(player.FullName, player);
break;
case "Silver":
player.Category = Category.Silver;
player.FullName = cellValue;
player.Points = Convert.ToInt32(row[result.Columns[i + 1]].ToString());
silverPlayers.Add(player.FullName, player);
break;
case "Bronze A":
player.Category = Category.Bronze_A;
player.FullName = cellValue;
player.Points = Convert.ToInt32(row[result.Columns[i + 1]].ToString());
bronzeAPlayers.Add(player.FullName, player);
break;
case "Bronze B":
player.Category = Category.Bronze_B;
player.FullName = cellValue;
player.Points = Convert.ToInt32(row[result.Columns[i + 1]].ToString());
bronzeBPlayers.Add(player.FullName, player);
break;
}
}
}
}
var categories = new Dictionary <Category, List <Player> >
{
{ Category.Gold, goldPlayers.Values.ToList() },
{ Category.Silver, silverPlayers.Values.ToList() },
{ Category.Bronze_A, bronzeAPlayers.Values.ToList() },
{ Category.Bronze_B, bronzeBPlayers.Values.ToList() },
};
return(categories);
}
private void button1_Click(object sender, EventArgs e)
{
try
{
string sql = null;
string data = null;
int i = 0;
int j = 0;
Exce.Application xlApp;
Exce.Workbook xlWorkBook;
Exce.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlApp = new Exce.Application();
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Exce.Worksheet)xlWorkBook.Worksheets.get_Item(1);
connection.Open();
sql = "SELECT city_name, zip_code, state, country, area FROM city";
OleDbDataAdapter dscmd = new OleDbDataAdapter(sql, connection);
DataSet ds = new DataSet();
dscmd.Fill(ds);
xlWorkSheet.Cells[1, 1] = "City Name";
xlWorkSheet.Cells[1, 2] = "Zip Code";
xlWorkSheet.Cells[1, 3] = "State";
xlWorkSheet.Cells[1, 4] = "Country";
xlWorkSheet.Cells[1, 5] = "Area";
for (i = 0; i <= ds.Tables[0].Rows.Count - 1; i++)
{
for (j = 0; j <= ds.Tables[0].Columns.Count - 1; j++)
{
data = ds.Tables[0].Rows[i].ItemArray[j].ToString();
xlWorkSheet.Cells[i + 2, j + 1] = data;
}
}
xlWorkBook.SaveAs("City Report.xls", Exce.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Exce.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
MessageBox.Show("Excel file created , you can find the file C:\\Users\\User\\Documents. City Report.xls");
}
catch (Exception)
{
}
finally
{
connection.Close();
}
}
public void ReadData()
{
//TODO: check what is required on the machine and maybe support for other versions
string ConnString = GetConnectionString();
string sql = "";
using (OleDbConnection Conn = new OleDbConnection(ConnString))
{
try
{
Conn.Open();
}
catch (Exception ex)
{
System.Threading.Thread.Sleep(3000);
Conn.Open();
Reporter.ToLog(eLogLevel.ERROR, $"Method - {MethodBase.GetCurrentMethod().Name}, Error - {ex.StackTrace}", ex);
}
OleDbCommand Cmd = new OleDbCommand();
Cmd.Connection = Conn;
string SheetName = GetInputParamCalculatedValue("SheetName").Trim();
if (!SheetName.EndsWith("$"))
{
SheetName += "$";
}
if (SelectAllRows == false)
{
sql = "Select TOP 1 * from [" + SheetName + "]";
}
else
{
sql = "Select * from [" + SheetName + "]";
}
string where = GetInputParamCalculatedValue("SelectRowsWhere");
if (!string.IsNullOrEmpty(where))
{
sql += " WHERE " + where;
}
Cmd.CommandText = sql;
DataTable dt = new DataTable();
OleDbDataAdapter da = new OleDbDataAdapter();
da.SelectCommand = Cmd;
try
{
da.Fill(dt);
// we expect only 1 record
if (dt.Rows.Count == 1 && SelectAllRows == false)
{
DataRow r = dt.Rows[0];
for (int i = 0; i < dt.Columns.Count; i++)
{
AddOrUpdateReturnParamActual(dt.Columns[i].ColumnName, ((object)r[i]).ToString());
}
if (!String.IsNullOrEmpty(GetInputParamCalculatedValue("SetDataUsed")))
{
string rowKey = r[GetInputParamCalculatedValue("PrimaryKeyColumn")].ToString();
string updateSQL = @"UPDATE [" + GetInputParamCalculatedValue("SheetName") + "$] SET " + GetInputParamCalculatedValue("SetDataUsed") + " WHERE " + GetInputParamCalculatedValue("PrimaryKeyColumn") + "=" + rowKey + ";";
OleDbCommand myCommand = new OleDbCommand();
myCommand.Connection = Conn;
myCommand.CommandText = updateSQL;
myCommand.ExecuteNonQuery();
}
}
else if (dt.Rows.Count > 0 && SelectAllRows == true)
{
for (int j = 0; j < dt.Rows.Count; j++)
{
DataRow r = dt.Rows[j];
for (int i = 0; i < dt.Columns.Count; i++)
{
AddOrUpdateReturnParamActualWithPath(dt.Columns[i].ColumnName, ((object)r[i]).ToString(), "" + (j + 1).ToString());
}
}
if (!String.IsNullOrEmpty(GetInputParamCalculatedValue("SetDataUsed")))
{
string updateSQL = @"UPDATE [" + GetInputParamCalculatedValue("SheetName") + "$] SET " + GetInputParamCalculatedValue("SetDataUsed");
if (!string.IsNullOrEmpty(where))
{
updateSQL += " WHERE " + where + ";";
}
OleDbCommand myCommand = new OleDbCommand();
myCommand.Connection = Conn;
myCommand.CommandText = updateSQL;
myCommand.ExecuteNonQuery();
}
}
else if (dt.Rows.Count != 1 && SelectAllRows == false)
{
Status = Amdocs.Ginger.CoreNET.Execution.eRunStatus.Failed;
Error = "Excel Query should return only one row" + Environment.NewLine + sql + Environment.NewLine + "Returned: " + dt.Rows.Count + " Records";
}
}
catch (Exception ex)
{
this.Status = Amdocs.Ginger.CoreNET.Execution.eRunStatus.Failed;
Error = ex.Message;
}
finally
{
Conn.Close();
}
if (dt.Rows.Count == 0)
{
this.Status = Amdocs.Ginger.CoreNET.Execution.eRunStatus.Failed;
Error = "No rows found in excel file matching criteria - " + sql;
}
}
}