/// <inheritdoc/>
public IEnumerable<int> GetSelectedRowIndicesFromTo(int startIndex, int maxIndexExclusive, DataColumnCollection table, int totalRowCount)
{
int endExclusive = Math.Min(maxIndexExclusive, totalRowCount);
for (int r = startIndex; r < endExclusive; ++r)
yield return r;
}
private static DataSet GetConfigurationDataSet()
{
DataSet configuration = null;
bool configException = false;
Ticks startTime = DateTime.UtcNow.Ticks;
Time elapsedTime;
string nodeIDQueryString = null;
switch (s_configurationType)
{
case ConfigurationType.Database:
// Attempt to load configuration from a database connection
IDbConnection connection = null;
Dictionary <string, string> settings;
string assemblyName, connectionTypeName, adapterTypeName;
string setting;
Assembly assembly;
Type connectionType, adapterType;
DataTable entities, source, destination;
try
{
settings = s_dataProviderString.ParseKeyValuePairs();
assemblyName = settings["AssemblyName"].ToNonNullString();
connectionTypeName = settings["ConnectionType"].ToNonNullString();
adapterTypeName = settings["AdapterType"].ToNonNullString();
if (string.IsNullOrWhiteSpace(connectionTypeName))
{
throw new InvalidOperationException("Database connection type was not defined.");
}
if (string.IsNullOrWhiteSpace(adapterTypeName))
{
throw new InvalidOperationException("Database adapter type was not defined.");
}
assembly = Assembly.Load(new AssemblyName(assemblyName));
connectionType = assembly.GetType(connectionTypeName);
adapterType = assembly.GetType(adapterTypeName);
connection = (IDbConnection)Activator.CreateInstance(connectionType);
connection.ConnectionString = s_connectionString;
connection.Open();
configuration = new DataSet("Iaon");
// Load configuration entities defined in database
entities = connection.RetrieveData(adapterType, "SELECT * FROM ConfigurationEntity WHERE Enabled <> 0 ORDER BY LoadOrder");
entities.TableName = "ConfigurationEntity";
// Add configuration entities table to system configuration for reference
configuration.Tables.Add(entities.Copy());
// Get the node ID query string
if (settings.TryGetValue("Provider", out setting))
{
// Check if provider is for Access since it uses braces as Guid delimiters
if (setting.StartsWith("Microsoft.Jet.OLEDB", StringComparison.OrdinalIgnoreCase))
{
nodeIDQueryString = "{" + s_nodeID + "}";
// Make sure path to Access database is fully qualified
if (settings.TryGetValue("Data Source", out setting))
{
settings["Data Source"] = FilePath.GetAbsolutePath(setting);
s_connectionString = settings.JoinKeyValuePairs();
}
}
}
if (string.IsNullOrWhiteSpace(nodeIDQueryString))
{
nodeIDQueryString = "'" + s_nodeID + "'";
}
Ticks operationStartTime;
Time operationElapsedTime;
// Add each configuration entity to the system configuration
foreach (DataRow entityRow in entities.Rows)
{
// Load configuration entity data filtered by node ID
operationStartTime = DateTime.UtcNow.Ticks;
source = connection.RetrieveData(adapterType, string.Format("SELECT * FROM {0} WHERE NodeID={1}", entityRow["SourceName"], nodeIDQueryString));
operationElapsedTime = (DateTime.UtcNow.Ticks - operationStartTime).ToSeconds();
// Update table name as defined in configuration entity
source.TableName = entityRow["RuntimeName"].ToString();
DisplayStatusMessage("Loaded {0} row{1} from \"{2}\" in {3}...", UpdateType.Information, source.Rows.Count, source.Rows.Count == 1 ? "" : "s", source.TableName, operationElapsedTime.ToString(2));
operationStartTime = DateTime.UtcNow.Ticks;
// Clone data source
destination = source.Clone();
// Get destination column collection
DataColumnCollection columns = destination.Columns;
// Remove redundant node ID column
columns.Remove("NodeID");
// Pre-cache column index translation after removal of NodeID column to speed data copy
Dictionary <int, int> columnIndex = new Dictionary <int, int>();
foreach (DataColumn column in columns)
{
columnIndex[column.Ordinal] = source.Columns[column.ColumnName].Ordinal;
}
// Manually copy-in each row into table
foreach (DataRow sourceRow in source.Rows)
{
DataRow newRow = destination.NewRow();
// Copy each column of data in the current row
for (int x = 0; x < columns.Count; x++)
{
newRow[x] = sourceRow[columnIndex[x]];
}
// Add new row to destination table
destination.Rows.Add(newRow);
}
operationElapsedTime = (DateTime.UtcNow.Ticks - operationStartTime).ToSeconds();
// Add entity configuration data to system configuration
configuration.Tables.Add(destination);
DisplayStatusMessage("{0} configuration pre-cache completed in {1}.", UpdateType.Information, source.TableName, operationElapsedTime.ToString(2));
}
DisplayStatusMessage("Database configuration successfully loaded.", UpdateType.Information);
}
catch (Exception ex)
{
configException = true;
DisplayStatusMessage("Failed to load database configuration due to exception: {0}", UpdateType.Warning, ex.Message);
}
finally
{
if (connection != null)
{
connection.Dispose();
}
DisplayStatusMessage("Database configuration connection closed.", UpdateType.Information);
}
break;
case ConfigurationType.WebService:
// Attempt to load configuration from webservice based connection
WebRequest request = null;
Stream response = null;
try
{
DisplayStatusMessage("Webservice configuration connection opened.", UpdateType.Information);
configuration = new DataSet();
request = WebRequest.Create(s_connectionString);
response = request.GetResponse().GetResponseStream();
configuration.ReadXml(response);
DisplayStatusMessage("Webservice configuration successfully loaded.", UpdateType.Information);
}
catch (Exception ex)
{
configException = true;
DisplayStatusMessage("Failed to load webservice configuration due to exception: {0}", UpdateType.Warning, ex.Message);
}
finally
{
if (response != null)
{
response.Dispose();
}
DisplayStatusMessage("Webservice configuration connection closed.", UpdateType.Information);
}
break;
case ConfigurationType.BinaryFile:
// Attempt to load cached binary configuration file
try
{
DisplayStatusMessage("Loading binary based configuration from \"{0}\".", UpdateType.Information, s_connectionString);
configuration = Serialization.Deserialize <DataSet>(File.OpenRead(s_connectionString), SerializationFormat.Binary);
DisplayStatusMessage("Binary based configuration successfully loaded.", UpdateType.Information);
}
catch (Exception ex)
{
configException = true;
DisplayStatusMessage("Failed to load binary based configuration due to exception: {0}.", UpdateType.Alarm, ex.Message);
}
break;
case ConfigurationType.XmlFile:
// Attempt to load cached XML configuration file
try
{
DisplayStatusMessage("Loading XML based configuration from \"{0}\".", UpdateType.Information, s_connectionString);
configuration = new DataSet();
configuration.ReadXml(s_connectionString);
DisplayStatusMessage("XML based configuration successfully loaded.", UpdateType.Information);
}
catch (Exception ex)
{
configException = true;
DisplayStatusMessage("Failed to load XML based configuration due to exception: {0}.", UpdateType.Alarm, ex.Message);
}
break;
}
if (!configException)
{
elapsedTime = (DateTime.UtcNow.Ticks - startTime).ToSeconds();
DisplayStatusMessage("{0} configuration load process completed in {1}...", UpdateType.Information, s_configurationType, elapsedTime.ToString(2));
}
return(configuration);
}
internal DataTable BuildSchemaTable()
{
int columns = _rs.ColumnCount;
DataTable schema = CreateSchemaTable(null, columns);
for (int i = 0; i < columns; i++)
{
string column = _rs.Label[i];
Database db = _command._connection.InternalDatabase;
Table table = db.GetTable(_rs.Table[i], _command._connection.Channel);
int index = table.GetColumnNumber(column);
Column col = table.GetColumn(index);
DataRow row = schema.NewRow();
row["ColumnName"] = column;
row["Ordinal"] = i;
row["Size"] = 0;
row["ProviderType"] = _rs.Type[i];
row["DataType"] = GetDataType(_rs.Type[i]);
row["AllowDBNull"] = col.IsNullable;
row["IsIdentity"] = col.IsIdentity;
row["IsAutoIncrement"] = col.IsIdentity;
if (this._browseModeInfoConsumed)
{
row["IsAliased"] = (_rs.Label[i] != _rs.Name[i]);
Index ix = table.PrimaryIndex;
if (ix != null)
{
ArrayList a = new ArrayList(ix.Columns);
row["IsKey"] = (a.IndexOf(i) != -1);
}
else
{
row["IsKey"] = false;
}
row["IsHidden"] = "";
row["IsExpression"] = (_rs.Table[i] == null);
}
row["IsLong"] = false; //data1.metaType.IsLong;
if (col.ColumnType == ColumnType.Timestamp)
{
row["IsUnique"] = true;
row["IsRowVersion"] = true;
}
else
{
row["IsUnique"] = false;
row["IsRowVersion"] = false;
}
row["IsReadOnly"] = false;
row["Precision"] = 0;
row["Scale"] = 0;
row["BaseServerName"] = null;
row["BaseCatalogName"] = db.Name;
row["BaseSchemaName"] = null;
row["BaseTableName"] = table.Name;
row["BaseColumnName"] = column;
schema.Rows.Add(row);
}
DataColumnCollection c = schema.Columns;
for (int i = 0; i < c.Count; i++)
{
c[i].ReadOnly = true;
}
return(schema);
}
private void setPageState()
{
BusinessServices.Organisation objOrg = new BusinessServices.Organisation();
BusinessServices.Course objCourse = new BusinessServices.Course();
// set datasource
DataTable dtCourse = objCourse.GetCourseListAccessableToOrg(UserContext.UserData.OrgID, 1);
// set datatext field
dtCourse.Select("active = 1");
chkCourseList.DataSource = dtCourse;
chkCourseList.DataTextField = "Name";
chkCourseList.DataValueField = "CourseID";
chkCourseList.DataBind();
//set datavalue field
ListItem itm = null;
itm = chkCourseList.Items.FindByValue(Session["RemEscID"].ToString());
if (itm != null)
{
// updating existing reminders
itm.Selected = true;
chkCourseList.Enabled = false;
// get the reminder escalation stuff from the db and display
DataTable dtr = objOrganisation.getEscalationConfigForCourse(UserContext.UserData.OrgID, Int32.Parse(itm.Value));
DataColumnCollection dc = dtr.Columns;
DataRow drw = dtr.Rows[0];
//drw.ItemArray[""];
// initial enrolment stuff
chkWarnUsers.Checked = (bool)drw.ItemArray[dc.IndexOf("remindusers")];
txtNumberofDaysDelinq.Text = drw.ItemArray[dc.IndexOf("daystocompletecourse")].ToString().Equals("0") ? "" : drw.ItemArray[dc.IndexOf("daystocompletecourse")].ToString();
// pre expiry stuff
chkWarnQuizExpiry.Checked = (bool)drw.ItemArray[dc.IndexOf("quizExpiryWarn")];
txtDaysBeforeWarn.Text = drw.ItemArray[dc.IndexOf("daysquizexpiry")].ToString().Equals("0") ? "" : drw.ItemArray[dc.IndexOf("daysquizexpiry")].ToString();
chkPreExpInitEnrolment.Checked = (bool)drw.ItemArray[dc.IndexOf("PreExpInitEnrolment")];
chkPreExpiryResitPeriod.Checked = (bool)drw.ItemArray[dc.IndexOf("PreExpResitPeriod")];
// post expiry
chkPostExpiryReminder.Checked = (bool)drw.ItemArray[dc.IndexOf("PostExpReminder")];
txtNumberOfWarns.Text = drw.ItemArray[dc.IndexOf("NumOfRemNotfy")].ToString().Equals("0") ? "" : drw.ItemArray[dc.IndexOf("NumOfRemNotfy")].ToString();
txtNumberofDaysWarns.Text = drw.ItemArray[dc.IndexOf("RepeatRem")].ToString().Equals("0") ? "" : drw.ItemArray[dc.IndexOf("RepeatRem")].ToString();
chkPostExpiryInit.Checked = (bool)drw.ItemArray[dc.IndexOf("PostExpInitEnrolment")];
chkPostExpiryResit.Checked = (bool)drw.ItemArray[dc.IndexOf("PostExpResitPeriod")];
//manager check
chkWarnMgrs.Checked = (bool)drw.ItemArray[dc.IndexOf("NotifyMgr")];;
if (chkWarnMgrs.Checked)
{
radEscMgrMailOpts.Enabled = true;
if ((bool)dtr.Rows[0]["IndividualNotification"]) // if its individual notification select the first radio option
{
radEscMgrMailOpts.SelectedIndex = 0;
chkCumulative.Checked = false;
chkCumulative.Enabled = false;
txtREegularDays.Text = "";
txtREegularDays.Enabled = false;
}
else // regular list every X days
{
radEscMgrMailOpts.SelectedIndex = 1;
chkCumulative.Checked = (bool)dtr.Rows[0]["IsCumulative"];
txtREegularDays.Text = dtr.Rows[0]["NotifyMgrDays"].ToString();
txtREegularDays.Enabled = true;
chkCumulative.Enabled = true;
}
}
else
{
radEscMgrMailOpts.SelectedIndex = -1;
radEscMgrMailOpts.Enabled = false;
chkCumulative.Checked = false;
chkCumulative.Enabled = false;
txtREegularDays.Text = "";
txtREegularDays.Enabled = false;
txtDaysBeforeWarn.Enabled = false;
}
enableDisable();
}
else
{
//creating new one
chkWarnUsers.Checked = false;
chkWarnMgrs.Checked = false;
chkWarnQuizExpiry.Checked = false;
txtNumberofDaysDelinq.Text = "";
lblValidationMSG.Text = "";
enableDisable();
}
//if (objOrg.orgMailFlagConfig(UserContext.UserData.OrgID, 0, UserContext.UserID))
//{
// btnMailFlag.Text = ResourceManager.GetString("btnStopEmail_Start");
//}
//else
//{
// btnMailFlag.Text = ResourceManager.GetString("btnStopEmail_Stop");
//}
}
/// Imports and processes the input CSV file, i.e. fills the internal MetaCard hash tables and fetches product info from MKM if necessary.
/// Intended to run in a separate thread.
/// <param name="filePath">The file path.</param>
private void importRun(string filePath, string defaultFoil, string defaultPlayset, string defaultSigned, string defaultAltered,
string defaultCondition, string defaultExpansion, string defaultLanguageID)
{
DataTable dt;
try
{
dt = MKMCsvUtils.ConvertCSVtoDataTable(filePath);
}
catch (Exception eError)
{
LogError("importing file " + filePath, eError.Message, true);
return;
}
importedColumns = dt.Columns;
MainView.Instance.LogMainWindow("Loaded file with " + dt.Rows.Count + " articles, processing...");
importedAll.Clear();
importedValidOnly.Clear();
int counter = 0, failed = 0, hasPriceGuide = 0;
// if we search for products based on their locName, we have to make a product query for each of them - store the result to reuse in case there are more of those cards later in the list
Dictionary <string, string> locNameProducts = new Dictionary <string, string>();
foreach (DataRow row in dt.Rows)
{
MKMMetaCard mc = new MKMMetaCard(row);
importedAll.Add(mc); // add it no matter if it can be correctly processed or not so that we can export it
counter++;
string productID = mc.GetAttribute(MCAttribute.ProductID);
string name = mc.GetAttribute(MCAttribute.Name);
string languageID = mc.GetAttribute(MCAttribute.LanguageID);
if (languageID == "" && defaultLanguageID != "")
{
languageID = defaultLanguageID;
mc.SetLanguageID(languageID);
}
if (name == "" && productID == "") // we have neither name or productID - we have to hope we have locName and language
{
string locName = mc.GetAttribute(MCAttribute.LocName).ToLower();
if (locName == "" || languageID == "")
{
LogError("importing line #" + (counter + 1) + ", article will be ignored",
"Neither product ID, English name, or localized name + language was found, cannot identify the card.", false);
failed++;
continue;
}
// have to use search on MKM to get the English name
string hash = "" + locName + languageID; // technically it is unlikely that two different languages would have the same name, but could happen
if (!locNameProducts.TryGetValue(hash, out name)) // we haven't had a product like this in the list yet, use MKM API to find it
{
int start = 0;
List <XmlNode> found = new List <XmlNode>();
try
{
XmlNodeList products;
do
{
XmlDocument doc = MKMInteract.RequestHelper.FindProducts(locName, languageID, start);
products = doc.GetElementsByTagName("product");
// we still want to insert empty string in the hash table - this way we know in the future that this name is invalid
locNameProducts[hash] = "";
foreach (XmlNode product in products)
{
// only take exact matches, otherwise we get all kinds of garbage like sleeves etc. that use the name of the card
if (product["locName"].InnerText.ToLower() == locName)
{
found.Add(product);
}
}
start += products.Count;
} while (products.Count == 100);
}
catch (Exception eError)
{
LogError("importing line #" + (counter + 1) + ", trying to find product by its localized name "
+ locName + ", article will be ignored", eError.Message, false);
failed++;
continue;
}
if (found.Count < 1)
{
LogError("importing line #" + (counter + 1) + ", trying to find product by its localized name "
+ locName + ", article will be ignored", "No article called " + locName + " in "
+ mc.GetAttribute(MCAttribute.Language) + " language found on MKM.", false);
failed++;
continue;
}
locNameProducts[hash] = name = found[0]["enName"].InnerText;
mc.SetAttribute(MCAttribute.Name, name);
}
else if (name != "")
{
mc.SetAttribute(MCAttribute.Name, name);
}
else
{
LogError("importing line #" + (counter + 1) + ", trying to find product by its localized name "
+ locName + ", article will be ignored", "" + locName + " is not a valid name", false);
failed++;
continue;
}
}
// process foil and condition now as it can be useful in determining expansion
string temp = mc.GetAttribute(MCAttribute.Foil);
Bool3 isFoil;
if (temp == "")
{
mc.SetBoolAttribute(MCAttribute.Foil, defaultFoil);
isFoil = ParseBool3(mc.GetAttribute(MCAttribute.Foil));
}
else
{
isFoil = ParseBool3(temp);
}
string condition = mc.GetAttribute(MCAttribute.Condition);
if (condition == "")
{
condition = defaultCondition;
mc.SetCondition(condition);
}
if (productID == "") // we now know we have the name, but we have to find out which expansion it is from to get the productID
{
string expID = mc.GetAttribute(MCAttribute.ExpansionID); // if the Expansion would be set, ExpansionID would be set as well in constructor of MKMMetaCard
if (expID == "") // we have to determine the expansion
{
var all = MKMDbManager.Instance.GetCardByName(name);
// options are: Latest, Oldest, Cheapest, Median Price, Most Expensive
if (all.GetEnumerator().MoveNext())
{
// used for prices based on price guide (cheapest, median, most expensive):
// for non-foil, compare the LOWEX+ for EX+ items, LOW for worse conditions, for foil compare the LOWFOIL, for "any foil" compare SELL
string priceGuidePrice;
if (isFoil == Bool3.True)
{
priceGuidePrice = "LOWFOIL";
}
else if (isFoil == Bool3.Any)
{
priceGuidePrice = "SELL";
}
else
{
if (IsBetterOrSameCondition(condition, "EX"))
{
priceGuidePrice = "LOWEX+";
}
else
{
priceGuidePrice = "LOW";
}
}
switch (defaultExpansion)
{
// for latest and oldest, we can just check local database
case "Latest":
DateTime latestTime = new DateTime(0);
foreach (DataRow dr in all)
{
string tempExpID = dr[MKMDbManager.InventoryFields.ExpansionID].ToString();
string releaseDate = MKMDbManager.Instance.GetExpansionByID(
tempExpID)[MKMDbManager.ExpansionsFields.ReleaseDate].ToString();
DateTime rel = DateTime.Parse(releaseDate, CultureInfo.InvariantCulture);
if (latestTime < rel)
{
latestTime = rel;
expID = tempExpID;
}
}
mc.SetAttribute(MCAttribute.ExpansionID, expID);
mc.SetAttribute(MCAttribute.Expansion,
MKMDbManager.Instance.GetExpansionByID(expID)[MKMDbManager.ExpansionsFields.Name].ToString());
break;
case "Oldest":
DateTime oldestTime = DateTime.Now;
foreach (DataRow dr in all)
{
string tempExpID = dr[MKMDbManager.InventoryFields.ExpansionID].ToString();
string releaseDate = MKMDbManager.Instance.GetExpansionByID(
tempExpID)[MKMDbManager.ExpansionsFields.ReleaseDate].ToString();
DateTime rel = DateTime.Parse(releaseDate, CultureInfo.InvariantCulture);
if (oldestTime > rel)
{
latestTime = rel;
expID = tempExpID;
}
}
mc.SetAttribute(MCAttribute.ExpansionID, expID);
mc.SetAttribute(MCAttribute.Expansion,
MKMDbManager.Instance.GetExpansionByID(expID)[MKMDbManager.ExpansionsFields.Name].ToString());
break;
// for the others we have to do product queries for each possibility
case "Cheapest":
XmlNode cheapestProduct = null; // we know all has at least one item so this will either get assigned or an exception is thrown and caught inside the foreach cycle
double cheapestPrice = double.MaxValue;
foreach (DataRow dr in all)
{
try
{
// there should always be exactly one product in the list
XmlNode product = MKMInteract.RequestHelper.GetProduct(
dr[MKMDbManager.InventoryFields.ProductID].ToString()).GetElementsByTagName("product")[0];
double price = double.Parse(product["priceGuide"][priceGuidePrice].InnerText);
if (price < cheapestPrice)
{
cheapestPrice = price;
cheapestProduct = product;
}
}
catch (Exception eError)
{
LogError("importing line #" + (counter + 1) + ", could not identify cheapest expansion for " + name + ", article will be ignored",
eError.Message, false);
failed++;
continue;
}
}
mc.FillProductInfo(cheapestProduct);
hasPriceGuide++;
break;
case "Median Price":
SortedList <double, XmlNode> prices = new SortedList <double, XmlNode>();
foreach (DataRow dr in all)
{
try
{
// there should always be exactly one product in the list
XmlNode product = MKMInteract.RequestHelper.GetProduct(
dr[MKMDbManager.InventoryFields.ProductID].ToString()).GetElementsByTagName("product")[0];
double price = double.Parse(product["priceGuide"][priceGuidePrice].InnerText);
prices.Add(price, product);
}
catch (Exception eError)
{
LogError("importing line #" + (counter + 1) + ", could not identify median price expansion for " + name + ", article will be ignored",
eError.Message, false);
failed++;
continue;
}
}
mc.FillProductInfo(prices.Values[prices.Count / 2]);
hasPriceGuide++;
break;
case "Most Expensive":
XmlNode mostExpProduct = null; // we know all has at least one item so this will either get assigned or an exception is thrown and caught inside the foreach cycle
double highestPrice = double.MinValue;
foreach (DataRow dr in all)
{
try
{
// there should always be exactly one product in the list
XmlNode product = MKMInteract.RequestHelper.GetProduct(
dr[MKMDbManager.InventoryFields.ProductID].ToString()).GetElementsByTagName("product")[0];
double price = double.Parse(product["priceGuide"][priceGuidePrice].InnerText);
if (price > highestPrice)
{
highestPrice = price;
mostExpProduct = product;
}
}
catch (Exception eError)
{
LogError("importing line #" + (counter + 1) + ", could not identify cheapest expansion for " + name + ", article will be ignored",
eError.Message, false);
failed++;
continue;
}
}
mc.FillProductInfo(mostExpProduct);
hasPriceGuide++;
break;
}
}
else
{
LogError("importing line #" + (counter + 1) + ", identifying expansion for " + name + ", article will be ignored",
"No card with this name found.", false);
failed++;
continue;
}
// TODO - determine whether the expansion is foil only / cannot be foil and based on the isFoil flag of the current article choose the correct set
}
// now we have expID and English name -> we can determine the product ID
string[] ids = MKMDbManager.Instance.GetCardProductID(name, mc.GetAttribute(MCAttribute.ExpansionID));
if (ids.Length == 0)
{
LogError("importing line #" + (counter + 1) + ", article will be ignored",
"The specified " + name + " and expansion ID " + expID + " do not match - product cannot be identified.", false);
failed++;
continue;
}
else if (ids.Length > 1)
{
string cardNumber = mc.GetAttribute(MCAttribute.CardNumber);
if (cardNumber == "")
{
LogError("importing line #" + (counter + 1) + ", article will be ignored", "The specified " + name +
" and expansion ID " + expID + " match multiple products - please provide Card Number to identify which one it is.", false);
failed++;
continue;
}
// search for the matching item
int start = 0;
try
{
XmlNodeList products;
do
{
XmlDocument doc = MKMInteract.RequestHelper.FindProducts(name, "1", start);
products = doc.GetElementsByTagName("product");
string expansion = mc.GetAttribute(MCAttribute.Expansion);
foreach (XmlNode product in products)
{
if (product["number"].InnerText == cardNumber && product["expansionName"].InnerText == expansion)
{
productID = product["idProduct"].InnerText;
// since we already have it, why not fill the product info in the MetaCard
mc.FillProductInfo(product);
break;
}
}
start += products.Count;
} while (products.Count == 100 && productID == "");
}
catch (Exception eError)
{
LogError("importing line #" + (counter + 1) + ", trying to find product ID for "
+ name + " based on its card number and expansion, article will be ignored", eError.Message, false);
failed++;
continue;
}
if (productID == "")
{
LogError("importing line #" + (counter + 1) + ", article will be ignored", "The specified " + name +
" and expansion ID " + expID
+ " match multiple products, Card Number was used to find the correct article, but no match was found, verify the data is correct.", false);
failed++;
continue;
}
}
else
{
productID = ids[0];
}
mc.SetAttribute(MCAttribute.ProductID, productID);
}
// if the defaults are "Any", there is not point in checking whether that attribute has been set or not
if (defaultPlayset != "")
{
temp = mc.GetAttribute(MCAttribute.Playset);
if (temp == "")
{
mc.SetBoolAttribute(MCAttribute.Playset, defaultPlayset);
}
}
if (defaultSigned != "")
{
temp = mc.GetAttribute(MCAttribute.Signed);
if (temp == "")
{
mc.SetBoolAttribute(MCAttribute.Signed, defaultSigned);
}
}
if (defaultAltered != "")
{
temp = mc.GetAttribute(MCAttribute.Altered);
if (temp == "")
{
mc.SetBoolAttribute(MCAttribute.Altered, defaultAltered);
}
}
// rarity might not be present in some cases, check it and get it from database, or worst case from MKM
var rarity = mc.GetAttribute(MCAttribute.Rarity);
if (rarity == "")
{
var dataRow = MKMDbManager.Instance.GetSingleCard(productID);
rarity = dataRow[MKMDbManager.InventoryFields.Rarity].ToString();
if (rarity == "")
{
try
{
var productDoc = MKMInteract.RequestHelper.GetProduct(productID);
rarity = productDoc["response"]["product"]["rarity"].InnerText;
dataRow[MKMDbManager.InventoryFields.Rarity] = rarity;
}
catch (Exception eError)
{
LogError("getting rarity for product " + productID, eError.Message, false);
}
}
mc.SetAttribute(MCAttribute.Rarity, rarity);
}
importedValidOnly.Add(mc);
if (checkBoxImportLog.Checked)
{
MainView.Instance.LogMainWindow("Imported line #" + (counter + 1) + ", " + name);
}
}
MainView.Instance.LogMainWindow("Card list " + filePath + " imported. Successfully imported " + importedValidOnly.Count +
" articles, failed to import: " + failed + ", articles that include MKM Price Guide: " + hasPriceGuide);
if (hasPriceGuide > 0)
{
priceGuidesGenerated = true;
}
}
public static IEnumerable <DataColumn> AsEnumerable(this DataColumnCollection source)
{
return(source.Cast <DataColumn>());
}
private void btnSave_Click(object sender, EventArgs e)
{
if (!btnSave.Enabled)
{
return;
}
Validator _validator = SCMS.Validators[this];
if (!Materia.Valid(_validator, txtDescription, !string.IsNullOrEmpty(txtDescription.Text.RLTrim()), "Please specify bank miscellaneous."))
{
return;
}
if (!Materia.Valid(_validator, cboAccount, cboAccount.SelectedIndex >= 0, "Please specify a valid account."))
{
return;
}
if (!Materia.Valid(_validator, cboType, cboType.SelectedIndex >= 0, "Please specify a valid type."))
{
return;
}
DataTable _bankmisc = Cache.GetCachedTable("bankmiscellaneous");
if (_bankmisc != null)
{
DataRow[] _rows = _bankmisc.Select("([BankMiscellaneous] LIKE '" + txtDescription.Text.ToSqlValidString(true) + "') AND\n" +
"NOT ([BankMiscellaneous] LIKE '" + _bankmiscellaneous.ToSqlValidString(true) + "')");
if (!Materia.Valid(_validator, txtDescription, _rows.Length <= 0, "Bank miscellaneous already exists."))
{
return;
}
string _query = "";
DataColumnCollection _cols = _bankmisc.Columns;
if (_isnew)
{
_query = "INSERT INTO `bankmiscellaneous`\n" +
"(`BankMiscellaneous`, `AccountCode`, `Type`, `DateCreated`)\n" +
"VALUES\n" +
"('" + txtDescription.Text.ToSqlValidString() + "', " + cboAccount.SelectedValue.ToString() + ", " + cboType.SelectedIndex.ToString() + ", NOW());";
object[] _values = new object[_cols.Count];
_values[_cols["BankMiscellaneous"].Ordinal] = txtDescription.Text;
_values[_cols["AccountCode"].Ordinal] = cboAccount.SelectedValue;
_values[_cols["Type"].Ordinal] = cboType.SelectedIndex;
_values[_cols["DateCreated"].Ordinal] = DateTime.Now;
_values[_cols["LastModified"].Ordinal] = DateTime.Now;
_bankmisc.Rows.Add(_values);
}
else
{
_query = "UPDATE `bankmiscellaneous` SET\n" +
"`BankMiscellaneous` = '" + txtDescription.Text.ToSqlValidString() + "', `AccountCode` = " + cboAccount.SelectedValue.ToString() + ", `Type` = " + cboType.SelectedIndex.ToString() + "\n" +
"WHERE\n" +
"(`BankMiscellaneous` LIKE '" + _bankmiscellaneous.ToSqlValidString() + "');";
DataRow[] _existing = _bankmisc.Select("[BankMiscellaneous] LIKE '" + _bankmiscellaneous.ToSqlValidString(true) + "'");
if (_existing.Length > 0)
{
_existing[0]["BankMiscellaneous"] = txtDescription.Text;
_existing[0]["AccountCode"] = cboAccount.SelectedValue;
_existing[0]["Type"] = cboType.SelectedIndex;
}
}
if (!string.IsNullOrEmpty(_query.RLTrim()))
{
btnSave.Enabled = false; btnSaveAndClose.Enabled = false;
IAsyncResult _result = Que.BeginExecution(SCMS.Connection, _query);
while (!_result.IsCompleted &&
!_cancelled)
{
Thread.Sleep(1); Application.DoEvents();
}
if (_cancelled)
{
if (!_result.IsCompleted)
{
try { _result = null; }
catch { }
finally { Materia.RefreshAndManageCurrentProcess(); }
}
return;
}
else
{
QueResult _queresult = Que.EndExecution(_result);
if (string.IsNullOrEmpty(_queresult.Error.RLTrim()))
{
UserAction _action = UserAction.Add;
if (!_isnew)
{
_action = UserAction.Edit;
}
string _log = "Added a new bank miscellaneous : " + txtDescription.Text + ".";
if (!_isnew)
{
_log = "Updated bank miscellaneous : " + _bankmiscellaneous + (_bankmiscellaneous != txtDescription.Text ? " to " + txtDescription.Text : "").ToString() + ".";
}
_bankmisc.AcceptChanges(); _bankmiscellaneous = txtDescription.Text;
if (_isnew)
{
_isnew = false;
}
if (_updated)
{
_updated = false;
}
if (!_withupdates)
{
_withupdates = true;
}
Text = Text.Replace(" *", "").Replace("*", "");
Cursor = Cursors.WaitCursor;
IAsyncResult _logresult = SCMS.CurrentSystemUser.LogActionAsync(_action, _log);
_logresult.WaitToFinish();
Cursor = Cursors.Default;
if (sender == btnSaveAndClose)
{
DialogResult = System.Windows.Forms.DialogResult.OK; Close();
}
}
else
{
if (_queresult.Error.Contains("duplicate"))
{
bool _invalid = Materia.Valid(_validator, txtDescription, false, "Bank miscellaneous already exists.");
}
else
{
SCMS.LogError(this.GetType().Name, new Exception(_queresult.Error));
MsgBoxEx.Alert("Failed to save the current bank miscellaneous.", "Save Bank Miscellaneous");
}
_bankmisc.RejectChanges();
}
_queresult.Dispose();
}
btnSave.Enabled = true; btnSaveAndClose.Enabled = true;
}
}
else
{
if (sender == btnSaveAndClose)
{
DialogResult = System.Windows.Forms.DialogResult.None; Close();
}
}
}
/// <inheritdoc/>
public IEnumerable<int> GetSelectedRowIndicesFromTo(int startIndex, int maxIndexExclusive, DataColumnCollection table, int totalRowCount)
{
var column = _columnProxy?.Document;
if (null == column)
yield break;
int endExclusive = Math.Min(maxIndexExclusive, totalRowCount);
if (column.Count.HasValue)
endExclusive = Math.Min(endExclusive, column.Count.Value);
for (int i = startIndex; i < endExclusive; ++i)
{
if (column.IsElementEmpty(i))
continue;
var x = column[i];
if (_isLowerInclusive)
{
if (!(x >= _lowerValue))
continue;
}
else
{
if (!(x > _lowerValue))
continue;
}
if (_isUpperInclusive)
{
if (!(x <= _upperValue))
continue;
}
else
{
if (!(x < _upperValue))
continue;
}
yield return i;
}
}
public List <string> TableScripts()
{
List <string> res = new List <string>();
NpgsqlConnection serverConnection = new NpgsqlConnection(ConnectString);
serverConnection.Open();
List <string> tables = new List <string>();
DataTable dt = serverConnection.GetSchema("Tables");
foreach (DataRow row in dt.Rows)
{
string tablename = (string)row[2];
tables.Add(tablename);
// SystemLog.Write(tablename);
string SelectString = @"SELECT TOP 1 * FROM " + tablename;
NpgsqlDataAdapter serverAdapter = new NpgsqlDataAdapter();
serverAdapter.SelectCommand = new NpgsqlCommand(SelectString, serverConnection);
DataSet datasetServer = new DataSet();
serverAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
serverAdapter.Fill(datasetServer, tablename);
DataColumn[] primaryKeys = datasetServer.Tables[tablename].PrimaryKey;
string createTable = "CREATE TABLE " + tablename + "(";
DataColumnCollection columns = datasetServer.Tables[tablename].Columns;
int counter = 0;
foreach (DataColumn c in columns)
{
string type = "";
if (c.DataType == typeof(int))
{
type = "int";
}
else if (c.DataType == typeof(double))
{
type = "float";
}
else if (c.DataType == typeof(string))
{
type = "ntext";
}
else if (c.DataType == typeof(DateTime))
{
type = "datetime";
}
else if (c.DataType == typeof(Boolean))
{
type = "bit";
}
else
{
type = "UNKWON";
}
createTable += (counter == 0 ? "" : ",") + c.ToString() + " " + type;
counter++;
}
if (primaryKeys != null && primaryKeys.Length > 0)
{
createTable += ", PRIMARY KEY(";
counter = 0;
foreach (DataColumn c in primaryKeys)
{
createTable += (counter == 0 ? "" : ", ") + c.ToString();
counter++;
}
createTable += ")";
}
createTable += ")";
res.Add(createTable);
}
serverConnection.Close();
return(res);
}
internal MetaDataColumnCollection(DataColumnCollection columns)
{
_columns = columns;
}
public void UpdateDataTable(DataTable table)
{
lock (objLock)
{
using (NpgsqlConnection _connectionInternal = new NpgsqlConnection(ConnectString))
{
try
{
if (_connectionInternal.State != ConnectionState.Open)
{
_connectionInternal.Open();
}
DataTable changes = table.GetChanges(DataRowState.Added);
if (changes != null)
{
DataRowCollection rows = changes.Rows;
DataColumnCollection cols = changes.Columns;
int colLength = cols.Count;
int rowLength = rows.Count;
string insert = @"INSERT INTO " + table.TableName + "(";
for (int i = 0; i < colLength; i++)
{
insert += cols[i].ColumnName + ",";
}
insert = insert.Substring(0, insert.Length - 1) + ")";
var transaction = _connectionInternal.BeginTransaction();
for (int j = 0; j < rowLength; j++)
{
DataRow row = rows[j];
string addstring = insert + " VALUES(";
for (int i = 0; i < colLength; i++)
{
DataColumn col = cols[i];
if (col.DataType == typeof(string) || col.DataType == typeof(char))
{
addstring += "'" + row[col] + "' , ";
}
else if (col.DataType == typeof(bool))
{
addstring += ((bool)row[col] ? 1 : 0) + " , ";
}
else if (col.DataType == typeof(DateTime))
{
addstring += string.IsNullOrWhiteSpace(row[col].ToString()) ? "null, " : "'" + ((DateTime)row[col]).ToString("yyyy-MM-dd HH:mm:ss.fff") + "' , ";
}
else
{
addstring += (string.IsNullOrWhiteSpace(row[col].ToString()) ? "null" : row[col].ToString()) + " , ";
}
}
addstring = addstring.Substring(0, addstring.Length - 2) + ");";
NpgsqlCommand command = new NpgsqlCommand(addstring, _connectionInternal, transaction);
command.ExecuteNonQuery();
}
transaction.Commit();
table.AcceptChanges();
}
changes = table.GetChanges(DataRowState.Modified);
if (changes != null)
{
DataRowCollection rows = changes.Rows;
DataColumnCollection cols = changes.Columns;
DataColumn[] primary = changes.PrimaryKey;
int pl = primary.Length;
var names = new Dictionary <string, string>();
for (int i = 0; i < pl; i++)
{
var p = primary[i];
string name = p.ColumnName;
names.Add(name, "");
}
int colLength = cols.Count;
int rowLength = rows.Count;
var transaction = _connectionInternal.BeginTransaction();
for (int j = 0; j < rowLength; j++)
{
string update = @"UPDATE " + table.TableName + " SET ";
DataRow row = rows[j];
int added = 0;
for (int i = 0; i < colLength; i++)
{
var col = cols[i];
string name = col.ColumnName;
if (!names.ContainsKey(name))
{
added++;
string value = "";
if (col.DataType == typeof(string) || col.DataType == typeof(char))
{
value = "'" + row[col] + "' , ";
}
else if (col.DataType == typeof(bool))
{
value = ((bool)row[col] ? 1 : 0) + " , ";
}
else if (col.DataType == typeof(DateTime))
{
value = string.IsNullOrWhiteSpace(row[col].ToString()) ? "null, " : "'" + ((DateTime)row[col]).ToString("yyyy-MM-dd HH:mm:ss.fff") + "' , ";
}
else
{
value = (string.IsNullOrWhiteSpace(row[col].ToString()) ? "null" : row[col].ToString()) + " , ";
}
update += name + " = " + value;
}
}
if (added > 0)
{
update = update.Substring(0, update.Length - 2) + " WHERE ";
for (int i = 0; i < pl; i++)
{
var col = primary[i];
string name = col.ColumnName;
string value = "";
if (col.DataType == typeof(string) || col.DataType == typeof(char))
{
value = "'" + row[col] + "'";
}
else if (col.DataType == typeof(bool))
{
value = ((bool)row[col] ? 1 : 0).ToString();
}
else if (col.DataType == typeof(DateTime))
{
value = string.IsNullOrWhiteSpace(row[col].ToString()) ? "null" : "'" + ((DateTime)row[col]).ToString("yyyy-MM-dd HH:mm:ss.fff") + "' ";
}
else
{
value = (string.IsNullOrWhiteSpace(row[col].ToString()) ? "null" : row[col].ToString());
}
update += name + " = " + value + (i == pl - 1 ? ";" : " AND ");
}
update = update.Trim();
if (update.EndsWith(", ;"))
{
update = update.Replace(", ;", ";");
}
NpgsqlCommand command = new NpgsqlCommand(update, _connectionInternal, transaction);
command.ExecuteNonQuery();
}
}
transaction.Commit();
table.AcceptChanges();
}
changes = table.GetChanges(DataRowState.Deleted);
if (changes != null)
{
DataRowCollection rows = changes.Rows;
DataColumnCollection cols = changes.Columns;
DataColumn[] primary = changes.PrimaryKey;
int pl = primary.Length;
var names = new Dictionary <string, string>();
for (int i = 0; i < pl; i++)
{
var p = primary[i];
string name = p.ColumnName;
names.Add(name, "");
}
int colLength = cols.Count;
int rowLength = rows.Count;
var transaction = _connectionInternal.BeginTransaction();
for (int j = 0; j < rowLength; j++)
{
DataRow row = rows[j];
// if(row.RowState != DataRowState.Deleted)
{
string update = @"DELETE FROM " + table.TableName + " WHERE ";
for (int i = 0; i < pl; i++)
{
// var col = primary[i];
// string name = col.ColumnName;
// string value = "";
// // Console.WriteLine(" -----> REMOVE: " + col + " " + name);
// if (col.DataType == typeof(string) || col.DataType == typeof(char))
// value = "'" + row[col, DataRowVersion.Original] + "' , ";
// else if (col.DataType == typeof(bool))
// value = ((bool)row[col, DataRowVersion.Original] ? 1 : 0).ToString();
// else if (col.DataType == typeof(DateTime))
// value = string.IsNullOrWhiteSpace(row[col, DataRowVersion.Original].ToString()) ? "null, " : "'" + ((DateTime)row[col]).ToString("yyyy-MM-dd HH:mm:ss.fff") + "' ";
// else
// value = (string.IsNullOrWhiteSpace(row[col, DataRowVersion.Original].ToString()) ? "null" : row[col].ToString());
// update += name + " = " + value + (i == pl - 1 ? ";" : " AND ");
var col = primary[i];
string name = col.ColumnName;
string value = "";
if (col.DataType == typeof(string) || col.DataType == typeof(char))
{
value = "'" + row[col, DataRowVersion.Original] + "'";
}
else if (col.DataType == typeof(bool))
{
value = ((bool)row[col, DataRowVersion.Original] ? 1 : 0).ToString();
}
else if (col.DataType == typeof(DateTime))
{
value = string.IsNullOrWhiteSpace(row[col, DataRowVersion.Original].ToString()) ? "null" : "'" + ((DateTime)row[col, DataRowVersion.Original]).ToString("yyyy-MM-dd HH:mm:ss.fff") + "' ";
}
else
{
value = (string.IsNullOrWhiteSpace(row[col, DataRowVersion.Original].ToString()) ? "null" : row[col, DataRowVersion.Original].ToString());
}
update += name + " = " + value + (i == pl - 1 ? ";" : " AND ");
}
update = update.Trim();
if (update.EndsWith(", ;"))
{
update = update.Replace(", ;", ";");
}
NpgsqlCommand command = new NpgsqlCommand(update, _connectionInternal, transaction);
command.ExecuteNonQuery();
}
}
transaction.Commit();
table.AcceptChanges();
}
_connectionInternal.Close();
}
catch (Exception e)
{
NpgsqlConnection.ClearAllPools();
// Console.WriteLine(e);
}
}
}
}
/// <summary>
/// This is needed to calculate the merge cells parameter to the excel export method
/// </summary>
/// <param name="row"></param>
/// <param name="columns"></param>
/// <returns></returns>
protected String getMergeCells(DataRow row, DataColumnCollection columns)
{
int mergeCounter = 0;
String mergeCells = "";
//merge cells have to be identified in the format row-column-mergecount
for (int i = 0; i < columns.Count; i++)
{
string colName = columns[i].ColumnName;
if (!row.Items.ContainsKey(colName))
continue;
string cellValue = row[colName].ToString();
//till first cell with content
if (mergeCells == "" && cellValue == "")
continue;
//first cell with content
else if (mergeCells == "" && cellValue != "")
mergeCells = mergeCells + "0-" + i.ToString() + "-";
//increment merge count till hit next cell with content
else if (mergeCells != "" && cellValue == "")
mergeCounter++;
else if (mergeCells != "" && cellValue != "")
{
mergeCells = mergeCells + mergeCounter.ToString() + "," + "0-" + i.ToString() + "-";
//reset merge counter
mergeCounter = 0;
}
}
//for last merge cell
mergeCells = mergeCells + mergeCounter.ToString();
return mergeCells;
}
internal DbSchemaTable(DataTable dataTable, bool returnProviderSpecificTypes)
{
_dataTable = dataTable;
_columns = dataTable.Columns;
_returnProviderSpecificTypes = returnProviderSpecificTypes;
}
/// <summary>
/// 通过数据行向当前XML元素下追加子元素
/// </summary>
/// <param name="xmlElement">被追加子元素的Xml元素</param>
/// <param name="dcc">当前数据表中的列集合</param>
/// <param name="dr">当前行数据</param>
/// <returns></returns>
public bool AppendChildElementByDataRow(ref XmlElement xmlElement, DataColumnCollection dcc, DataRow dr)
{
return(AppendChildElementByDataRow(ref xmlElement, dcc, dr, null));
}
public static void SaveDataSetAsExcel(DataTable dataTable, DataColumnCollection Columns, string exceloutFilePath)
{
using (var fs = new FileStream(exceloutFilePath, FileMode.Append, FileAccess.Write))
{
IWorkbook workbook = new XSSFWorkbook();
ISheet excelSheet = workbook.CreateSheet(dataTable.TableName);
List <string> columns = new List <string>();
IRow row = excelSheet.CreateRow(0);
int columnIndex = 0;
foreach (System.Data.DataColumn column in Columns)
{
//columns.Add(column.ColumnName);
row.CreateCell(columnIndex).SetCellValue(column.ColumnName);
columnIndex++;
}
columnIndex = 0;
foreach (System.Data.DataColumn column in dataTable.Columns)
{
columns.Add(column.ColumnName);
columnIndex++;
}
int rowIndex = 1;
XSSFCellStyle stylePercentage = null;
XSSFCellStyle styleCurrency = null;
foreach (DataRow dsrow in dataTable.Rows)
{
row = excelSheet.CreateRow(rowIndex);
int cellIndex = 0;
foreach (String col in columns)
{
var cell = row.CreateCell(cellIndex);
if (col == "MerchantPricingFeePercentage" || col == "MerchantPricingInterchangePercentRate")
{
stylePercentage = (XSSFCellStyle)workbook.CreateCellStyle();
stylePercentage.DataFormat = workbook.CreateDataFormat().GetFormat("0.00%");
cell.SetCellType(CellType.Numeric);
cell.CellStyle = stylePercentage;
if (dsrow[col].ToString() != "")
{
cell.SetCellValue(Convert.ToDouble(dsrow[col].ToString()));
}
}
else if (col == "MerchantPricingNetAmount" ||
col == "MerchantPricingFeePerItemRate" ||
col == "MerchantPricingInterchangePerItemRate" ||
col == "MerchantPricingTotalFees")
{
styleCurrency = (XSSFCellStyle)workbook.CreateCellStyle();
styleCurrency.DataFormat = workbook.CreateDataFormat().GetFormat("$#,##0.00");
cell.SetCellType(CellType.Numeric);
cell.CellStyle = styleCurrency;
if (dsrow[col].ToString() != "")
{
cell.SetCellValue(Convert.ToDouble(dsrow[col].ToString()));
}
}
else if (col == "GroupNumber" ||
col == "AssociationNumber" ||
col == "MerchantNumber" ||
col == "MerchantPricingMonthEndIDDisplay" ||
col == "MerchantPricingGrossCount")
{
cell.SetCellType(CellType.Numeric);
if (dsrow[col].ToString() != "")
{
cell.SetCellValue(Convert.ToDouble(dsrow[col].ToString()));
}
}
else
{
cell.SetCellValue(dsrow[col].ToString());
}
excelSheet.AutoSizeColumn(cellIndex);
cellIndex++;
}
rowIndex++;
}
workbook.Write(fs);
}
}
public string CreateTabScriptBySQL(string dbname, string strSQL)
{
this.dbobj.DbConnectStr = this._dbconnectStr;
string text = "TableName";
StringPlus stringPlus = new StringPlus();
int num = strSQL.IndexOf(" from ");
if (num > 0)
{
string text2 = strSQL.Substring(num + 5).Trim();
int num2 = text2.IndexOf(" ");
if (text2.Length > 0)
{
if (num2 > 0)
{
text = text2.Substring(0, num2).Trim();
}
else
{
text = text2.Substring(0).Trim();
}
}
}
text = text.Replace("[", "").Replace("]", "");
DataTable tabDataBySQL = this.dbobj.GetTabDataBySQL(dbname, strSQL);
if (tabDataBySQL != null)
{
DataColumnCollection columns = tabDataBySQL.Columns;
foreach (DataRow dataRow in tabDataBySQL.Rows)
{
StringPlus stringPlus2 = new StringPlus();
StringPlus stringPlus3 = new StringPlus();
foreach (DataColumn dataColumn in columns)
{
string columnName = dataColumn.ColumnName;
string name = dataColumn.DataType.Name;
bool arg_11D_0 = dataColumn.AutoIncrement;
string a;
if ((a = name.ToLower()) == null)
{
goto IL_1C6;
}
string text3;
if (!(a == "binary") && !(a == "byte[]") && !(a == "blob"))
{
if (!(a == "bit") && !(a == "boolean"))
{
goto IL_1C6;
}
text3 = ((dataRow[columnName].ToString().ToLower() == "true") ? "1" : "0");
}
else
{
byte[] bytes = (byte[])dataRow[columnName];
text3 = CodeCommon.ToHexString(bytes);
}
IL_1DB:
if (text3 != "")
{
if (CodeCommon.IsAddMark(name))
{
stringPlus3.Append("'" + text3.Replace("'", "''") + "',");
}
else
{
stringPlus3.Append(text3 + ",");
}
stringPlus2.Append("[" + columnName + "],");
continue;
}
continue;
IL_1C6:
text3 = dataRow[columnName].ToString().Trim();
goto IL_1DB;
}
stringPlus2.DelLastComma();
stringPlus3.DelLastComma();
stringPlus.Append("INSERT [" + text + "] (");
stringPlus.Append(stringPlus2.Value);
stringPlus.Append(") VALUES ( ");
stringPlus.Append(stringPlus3.Value);
stringPlus.AppendLine(")");
}
}
return(stringPlus.Value);
}
/// <summary>
/// 从DataTable的Columns构造
/// </summary>
/// <param name="columns"></param>
public WfClientRolePropertyDefinitionCollection(DataColumnCollection columns)
{
this.FromDataColumns(columns);
}
private object[] SetupMapping(int count, DataColumnCollection columnCollection, DataColumn chapterColumn, object chapterValue)
{
object[] dataValues = new object[count];
if (null == _indexMap)
{
int mappingCount = columnCollection.Count;
bool hasChapters = (null != _chapterMap);
if ((count != mappingCount) || hasChapters)
{
_mappedDataValues = new object[mappingCount];
if (hasChapters)
{
_mappedMode = MapChapters;
_mappedLength = count;
}
else
{
_mappedMode = MapDifferentSize;
_mappedLength = Math.Min(count, mappingCount);
}
}
else
{
_mappedMode = MapExactMatch; /* _mappedLength doesn't matter */
}
}
else
{
_mappedDataValues = new object[columnCollection.Count];
_mappedMode = ((null == _chapterMap) ? MapReorderedValues : MapChaptersReordered);
_mappedLength = count;
}
if (null != chapterColumn)
{ // value from parent tracked into child table
_mappedDataValues[chapterColumn.Ordinal] = chapterValue;
}
return dataValues;
}
public static void LoadDungeon(int dungeonNum, MySql database)
{
Dungeon dungeon = new Dungeon();
string query = "SELECT name, " +
"rescue " +
"FROM dungeon WHERE dungeon.num = \'" + dungeonNum + "\'";
DataColumnCollection row = database.RetrieveRow(query);
if (row != null)
{
dungeon.Name = row["name"].ValueString;
dungeon.AllowsRescue = row["rescue"].ValueString.ToBool();
}
query = "SELECT map_id, " +
"difficulty, " +
"boss_map " +
"FROM dungeon_smap WHERE dungeon_smap.num = \'" + dungeonNum + "\'";
foreach (DataColumnCollection columnCollection in database.RetrieveRowsEnumerable(query))
{
int mapID = columnCollection["map_id"].ValueString.ToInt();
StandardDungeonMap sMap = new StandardDungeonMap();
sMap.MapNum = mapID;
sMap.Difficulty = (Enums.JobDifficulty)columnCollection["difficulty"].ValueString.ToInt();
sMap.IsBadGoalMap = columnCollection["boss_map"].ValueString.ToBool();
dungeon.StandardMaps.Add(sMap);
}
query = "SELECT rdungeon_num, " +
"rdungeon_floor, " +
"difficulty, " +
"boss_map " +
"FROM dungeon_rmap WHERE dungeon_rmap.num = \'" + dungeonNum + "\'";
foreach (DataColumnCollection columnCollection in database.RetrieveRowsEnumerable(query))
{
int dungeonIndex = columnCollection["rdungeon_num"].ValueString.ToInt();
int dungeonFloor = columnCollection["rdungeon_floor"].ValueString.ToInt();
RandomDungeonMap rMap = new RandomDungeonMap();
rMap.RDungeonIndex = dungeonIndex;
rMap.RDungeonFloor = dungeonFloor;
rMap.Difficulty = (Enums.JobDifficulty)columnCollection["difficulty"].ValueString.ToInt();
rMap.IsBadGoalMap = columnCollection["boss_map"].ValueString.ToBool();
dungeon.RandomMaps.Add(rMap);
}
query = "SELECT script_key, " +
"script_args " +
"FROM dungeon_script WHERE dungeon_script.num = \'" + dungeonNum + "\'";
foreach (DataColumnCollection columnCollection in database.RetrieveRowsEnumerable(query))
{
int scriptKey = columnCollection["script_key"].ValueString.ToInt();
string scriptArgs = columnCollection["script_args"].ValueString;
dungeon.ScriptList.Add(scriptKey, scriptArgs);
}
dungeons.Dungeons.Add(dungeonNum, dungeon);
}
/// <summary>
/// Initializes a new instance of the DatabaseTableInfo class.
/// </summary>
/// <param name="row">The row definition.</param>
/// <param name="name">The table name.</param>
/// <param name="rowCount">The number of rows in the table.</param>
/// <param name="columns">Information on the column tables.</param>
public DatabaseTableInfo(
DataRow row,
string name,
int rowCount,
DataColumnCollection columns)
{
Name = name;
Data = row;
RowCount = rowCount;
Columns = columns;
}
private void loadGridData()
{
_dtPhase = MasterData.PhaseList_Get(includeArchive: false);
_dtWorkType = MasterData.WorkTypeList_Get(includeArchive: false);
DataTable dt = MasterData.WorkType_PhaseList_Get(workTypeID: this._qfWorkTypeID, phaseID: this._qfPhaseID);
if (dt != null)
{
this.DCC = dt.Columns;
Page.ClientScript.RegisterArrayDeclaration("_dcc", JsonConvert.SerializeObject(DCC, Newtonsoft.Json.Formatting.None));
ListItem item = null;
foreach (DataRow row in _dtPhase.Rows)
{
item = ddlQF.Items.FindByValue(row["PDDTDR_PhaseID"].ToString());
if (item == null)
{
ddlQF.Items.Add(new ListItem(row["PDDTDR_Phase"].ToString(), row["PDDTDR_PhaseID"].ToString()));
}
}
item = ddlQF.Items.FindByValue(_qfPhaseID.ToString());
if (item != null)
{
item.Selected = true;
}
foreach (DataRow row in _dtWorkType.Rows)
{
item = ddlQF_WorkType.Items.FindByValue(row["WorkTypeID"].ToString());
if (item == null)
{
ddlQF_WorkType.Items.Add(new ListItem(row["WorkType"].ToString(), row["WorkTypeID"].ToString()));
}
}
item = ddlQF_WorkType.Items.FindByValue(_qfWorkTypeID.ToString());
if (item != null)
{
item.Selected = true;
}
InitializeColumnData(ref dt);
dt.AcceptChanges();
}
if (_qfPhaseID != 0 && dt != null && dt.Rows.Count > 0)
{
dt.DefaultView.RowFilter = string.Format(" PDDTDR_PHASEID = {0}", _qfPhaseID.ToString());
dt = dt.DefaultView.ToTable();
}
if (_qfWorkTypeID != 0 && dt != null && dt.Rows.Count > 0)
{
dt.DefaultView.RowFilter = string.Format(" WorkTypeID = {0}", _qfWorkTypeID.ToString());
dt = dt.DefaultView.ToTable();
}
int count = dt.Rows.Count;
count = count > 0 ? count - 1 : count; //need to subtract the empty row
spanRowCount.InnerText = count.ToString();
grdMD.DataSource = dt;
grdMD.DataBind();
}
static List <TemplateField> GetListOfTemplateFieldsFromData(DataColumnCollection DataColumns, IWorkbook Workbook)
{
List <TemplateField> TemplateFields = new List <TemplateField>();
foreach (DataColumn Column in DataColumns)
{
ICellStyle CellStyle = Workbook.CreateCellStyle();
switch (Type.GetTypeCode(Column.DataType))
{
case TypeCode.Int16:
case TypeCode.Int32:
case TypeCode.Int64:
case TypeCode.UInt16:
case TypeCode.UInt32:
case TypeCode.UInt64:
case TypeCode.Double:
case TypeCode.Decimal:
TemplateFields.Add(new TemplateField
{
CellStyle = null,
CellType = CellType.Numeric,
IsDateFormmated = false
});
break;
case TypeCode.Boolean:
TemplateFields.Add(new TemplateField
{
CellStyle = null,
CellType = (Settings.OutputBitAsNumber) ? CellType.Numeric : CellType.Boolean,
IsDateFormmated = false
});
break;
case TypeCode.DateTime:
CellStyle.DataFormat = Workbook.CreateDataFormat().GetFormat(Settings.DefaultDateFormat);
TemplateFields.Add(new TemplateField
{
CellStyle = CellStyle,
CellType = CellType.Numeric,
IsDateFormmated = true
});
break;
case TypeCode.Char:
case TypeCode.String:
default:
TemplateFields.Add(new TemplateField
{
CellStyle = null,
CellType = CellType.String,
IsDateFormmated = false
});
break;
}
}
return(TemplateFields);
}
private bool WriteCsvFile(DataTable dt, string strPath, bool bWithHeader)
{
StringBuilder sb = new StringBuilder();
int i = 0;
DataColumnCollection dc = dt.Columns;
int cols = dc.Count;
if (bWithHeader)
{
for (i = 0; i < cols; i++)
{
if (i > 0)
{
sb.Append(",");
}
sb.Append(dc[i].ColumnName);
}
sb.Append("\r\n");
for (i = 0; i < cols; i++)
{
if (i > 0)
{
sb.Append(",");
}
sb.Append(dc[i].DataType.ToString().Replace("System.", ""));
}
sb.Append("\r\n");
}
DataRow dr = null;
for (i = 0; i < dt.Rows.Count; i++)
{
dr = dt.Rows[i];
for (int j = 0; j < cols; j++)
{
if (j > 0)
{
sb.Append(",");
}
string sValue = dr[j].ToString().Replace("\r\n", "@@eznz_return"); //encode line return in site_pages, kit...
sValue = sValue.Replace("\r", "@@eznz_return"); //encode single return
sValue = sValue.Replace("\n", "@@eznz_return"); //encode single return
sValue = sValue.Replace("@@eznz_return", "\\r\\n");
sb.Append("\"" + Program.EncodeDoubleQuote(sValue) + "\"");
}
sb.Append("\r\n");
}
// Encoding enc = Encoding.GetEncoding("iso-8859-1");
Encoding enc = Encoding.GetEncoding("gb2312");
byte[] Buffer = enc.GetBytes(sb.ToString());
if (File.Exists(strPath))
{
File.Delete(strPath);
}
StreamWriter sw = new StreamWriter(strPath, false, System.Text.Encoding.Unicode);
sw.Write(sb.ToString());
sw.Close();
return(true);
}
public GenericFormClass(string frameworkDescriptionValue, string firstLevelNameValue, string objectNameValue, DeklaritMode modeValue, DataSet formDataSetValue, DataColumnCollection columnCollectionValue, Control.ControlCollection controlsValue, ArrayList dataGridsValue, bool autoNumberValue)
{
this.frameworkDescription = frameworkDescriptionValue;
this.firstLevelName = firstLevelNameValue;
this.objectName = objectNameValue;
this.m_Mode = modeValue;
this.formDataSet = formDataSetValue;
this.columnCollection = columnCollectionValue;
this.m_Controls = controlsValue;
this.DataGrids = dataGridsValue;
this.autoNumber = autoNumberValue;
}
private bool WriteCsvFileActivata(DataTable dt, string strPath, bool bWithHeader)
{
StringBuilder sb = new StringBuilder();
int i = 0;
DataColumnCollection dc = dt.Columns;
int cols = dc.Count;
if (bWithHeader)
{
for (i = 0; i < cols; i++)
{
if (i > 0)
{
sb.Append(",");
}
sb.Append(dc[i].ColumnName);
}
sb.Append("\r\n");
for (i = 0; i < cols; i++)
{
if (i > 0)
{
sb.Append(",");
}
sb.Append(dc[i].DataType.ToString().Replace("System.", ""));
}
sb.Append("\r\n");
}
DataRow dr = null;
for (i = 0; i < dt.Rows.Count; i++)
{
string code = "";
string barcode = "";
dr = dt.Rows[i];
for (int j = 0; j < cols; j++)
{
if (j > 0)
{
sb.Append(",");
}
string sValue = dr[j].ToString();
if (dc[j].ColumnName == "code")
{
code = dr[j].ToString();
}
else if (dc[j].ColumnName == "barcode")
{
barcode = dr[j].ToString().Trim();
if (barcode == "")
{
int nLen = Program.g_sActivataStoreCode.Length + code.Length;
barcode = Program.g_sActivataStoreCode;
for (int n = nLen; n < 16; n++)
{
barcode += "0";
}
barcode += code;
}
sValue = barcode;
}
sb.Append("\"" + Program.EncodeDoubleQuote(sValue) + "\"");
}
sb.Append("\r\n");
}
// Encoding enc = Encoding.GetEncoding("iso-8859-1");
Encoding enc = Encoding.GetEncoding("gb2312");
byte[] Buffer = enc.GetBytes(sb.ToString());
if (File.Exists(strPath))
{
File.Delete(strPath);
}
StreamWriter sw = new StreamWriter(strPath, false, System.Text.Encoding.Unicode);
sw.Write(sb.ToString());
sw.Close();
return(true);
}
/// <inheritdoc/>
public IEnumerable <(int start, int endExclusive)> GetSelectedRowIndexSegmentsFromTo(int startIndex, int maxIndexExclusive, DataColumnCollection table, int totalRowCount)
{
var column = _columnProxy?.Document;
if (null == column)
{
yield break;
}
int endExclusive = Math.Min(maxIndexExclusive, totalRowCount);
if (column.Count.HasValue)
{
endExclusive = Math.Min(endExclusive, column.Count.Value);
}
bool weAreInsideSegment = false;
int indexOfStartOfSegment = 0;
for (int i = startIndex; i < endExclusive; ++i)
{
var x = column[i];
if (
column.IsElementEmpty(i) ||
(!(x == _value))
)
{
if (weAreInsideSegment)
{
yield return(indexOfStartOfSegment, i);
}
weAreInsideSegment = false;
continue;
}
else
{
// this is a index which should be included
if (!weAreInsideSegment)
{
indexOfStartOfSegment = i;
weAreInsideSegment = true;
}
}
}
// yield the last segment
if (weAreInsideSegment)
{
yield return(indexOfStartOfSegment, endExclusive);
}
}
void loadDataOld()
{
string fcmCode = TextUtils.ToString(cboFCM.EditValue);
txtTotalVT.EditValue = LibQLSX.ExcuteScalar("select sum(isnull(TotalBuy,0) * isnull(Price,0)) FROM [vRequirePartFull_Order] where ProjectCode = '" + fcmCode + "' and isnull([OrderCode],'')<>''");
int year = TextUtils.ToInt(cboYear.SelectedItem);
//int month = cboMonth.SelectedIndex;
DataTable dtLaiVay = loadLaiVay(fcmCode);
string sqlStart = "SELECT a.PK_ID, a.C_MA, a.C_MOTA, a.[GroupCode] +'-'+ a.[GroupName] as [GroupCode]";
string sqlEnd = " FROM V_DM_KMP a where isnull([FK_GROUP],0) > 0";
string sql = ",[C_Value] = isnull((select sum([C_PRICE]) from [V_DM_FCM_DETAIL] where [C_CODE] like '" + fcmCode + "%' and [C_KMP_MA] = a.C_MA),0)";
DataTable dtMonth = LibIE.Select("select distinct [C_Month] from [V_XNTC_REPORT_ALL] where [C_DTCP_MA] like '" + fcmCode + "%' and [C_YEAR] = " + year);
sql += " ,[Total_NamTruoc] = isnull((SELECT sum([C_PSNO]) FROM [V_XNTC_REPORT_ALL]"
+ " where [C_DTCP_MA] = '" + fcmCode + "' "
+ " and (FK_TKNO like '154%' or FK_TKNO like '6321%' or FK_TKNO like '642%' or FK_TKNO like '521%' or FK_TKNO like '635%')"
+ " and [C_KMP_MA] = a.C_MA "
+ " and [C_Year] < " + year + "),0)";
string exp = "Total_NamTruoc";
foreach (DataRow row in dtMonth.Rows)
{
string p = TextUtils.ToString(row["C_Month"]);
sql += " ,[T_" + p + "] = isnull((SELECT sum([C_PSNO]) FROM [V_XNTC_REPORT_ALL]"
+ " where [C_DTCP_MA] like '" + fcmCode + "%'"
+ " and (FK_TKNO like '154%' or FK_TKNO like '6321%' or FK_TKNO like '642%' or FK_TKNO like '521%' or FK_TKNO like '635%')"
+ " and [C_KMP_MA] = a.C_MA "
+ " and [C_Year] = " + year
+ " and [C_Month] = " + p + "),0) ";
exp += "+ T_" + p;
}
string str = sqlStart + sql + sqlEnd;
DataTable dtData = LibIE.Select(str);
#region Nhập chi phí vật tư thực tế của dự án
DataRow[] drsVT = dtData.Select("C_MA = 'C27' or C_MA = 'C20' or C_MA = 'C24' or C_MA = 'C22'");
foreach (DataRow r in drsVT)
{
string kmpCode = TextUtils.ToString(r["C_MA"]);
foreach (DataRow row in dtMonth.Rows)
{
string p = TextUtils.ToString(row["C_Month"]);
string sql1 = "SELECT sum([C_PSNO]) FROM [V_XNTC_REPORT_ALL]"
+ " where [C_DTCP_MA] like '" + fcmCode + "%' "
+ " and (FK_TKNO like '154%' or FK_TKNO like '6321%' or FK_TKNO like '642%' or FK_TKNO like '521%' or FK_TKNO like '635%')"
+ " and [C_KMP_MA] = '" + kmpCode + "'"
+ " and [C_Year] = " + year
+ " and [C_Month] = " + p;
string sql2 = "SELECT sum([C_PSNO]) FROM [V_XNTC_REPORT_ALL]"
+ " where [C_DTCP_MA_LUU] like '" + fcmCode + "%' "
+ " and (FK_TKNO = '154' AND FK_TKNO = '154')"
//+ " and [C_KMP_MA] = '" + kmpCode + "'"
+ " and [C_Year] = " + year
+ " and [C_Month] = " + p;
r["T_" + p] = TextUtils.ToDecimal(LibIE.ExcuteScalar(sql1)) + TextUtils.ToDecimal(LibIE.ExcuteScalar(sql2));
}
}
#endregion
#region Nhập chi phí lãi vay thực tế của dự án
DataRow[] drsLaiVay = dtData.Select("C_MA = 'C11'");
foreach (DataRow r in drsLaiVay)
{
string kmpCode = TextUtils.ToString(r["C_MA"]);
foreach (DataRow row in dtMonth.Rows)
{
int p = TextUtils.ToInt(row["C_Month"]);
decimal cLai = TextUtils.ToDecimal(dtLaiVay.Compute("SUM(LaiGop)", "C_Month = " + p + " and C_Year = " + year));
r["T_" + p] = cLai;
}
}
#endregion
DataColumnCollection columns = dtData.Columns;
DataTable dtFCM = LibIE.Select("select top 1 * from T_DM_FCM where C_CODE = '" + fcmCode + "'");
if (dtFCM.Rows.Count > 0)
{
#region Add column
DataRow dr1 = dtData.NewRow();
dr1["PK_ID"] = 0;
dr1["C_MA"] = "T1";
dr1["C_MOTA"] = "DT.Giá bán trên HĐ";
dr1["GroupCode"] = "";
dr1["C_Value"] = TextUtils.ToDecimal(dtFCM.Rows[0]["TotalHD"]);
if (columns.Contains("Total_NamTruoc"))
{
dr1["Total_NamTruoc"] = 0;
}
dtData.Rows.Add(dr1);
DataRow dr2 = dtData.NewRow();
dr2["PK_ID"] = 0;
dr2["C_MA"] = "T2";
dr2["C_MOTA"] = "DT.Giá bán theo quy định TPA";
dr2["GroupCode"] = "";
dr2["C_Value"] = TextUtils.ToDecimal(dtFCM.Rows[0]["TotalTPA"]);
if (columns.Contains("Total_NamTruoc"))
{
dr2["Total_NamTruoc"] = 0;
}
dtData.Rows.Add(dr2);
DataRow dr3 = dtData.NewRow();
dr3["PK_ID"] = 0;
dr3["C_MA"] = "T3";
dr3["C_MOTA"] = "DT.Thuế GTGT";
dr3["GroupCode"] = "";
dr3["C_Value"] = TextUtils.ToDecimal(dtFCM.Rows[0]["TotalVAT"]);
if (columns.Contains("Total_NamTruoc"))
{
dr3["Total_NamTruoc"] = 0;
}
dtData.Rows.Add(dr3);
DataRow dr4 = dtData.NewRow();
dr4["PK_ID"] = 0;
dr4["C_MA"] = "T4";
dr4["C_MOTA"] = "DT.Giá thực thu";
dr4["GroupCode"] = "";
dr4["C_Value"] = TextUtils.ToDecimal(dtFCM.Rows[0]["TotalReal"]);
if (columns.Contains("Total_NamTruoc"))
{
dr4["Total_NamTruoc"] = 0;
}
dtData.Rows.Add(dr4);
DataRow dr5 = dtData.NewRow();
dr5["PK_ID"] = 0;
dr5["C_MA"] = "T5";
dr5["C_MOTA"] = "DT.Tổng chi phí triển khai DA tại Khách Hàng";
dr5["GroupCode"] = "";
dr5["C_Value"] = TextUtils.ToDecimal(dtFCM.Rows[0]["TotalTrienKhai"]);
if (columns.Contains("Total_NamTruoc"))
{
dr5["Total_NamTruoc"] = 0;
}
dtData.Rows.Add(dr5);
DataRow dr6 = dtData.NewRow();
dr6["PK_ID"] = 0;
dr6["C_MA"] = "T6";
dr6["C_MOTA"] = "DT.Tổng chi phí nhân công trực tiếp";
dr6["GroupCode"] = "";
dr6["C_Value"] = TextUtils.ToDecimal(dtFCM.Rows[0]["TotalNC"]);
if (columns.Contains("Total_NamTruoc"))
{
dr6["Total_NamTruoc"] = 0;
}
dtData.Rows.Add(dr6);
DataRow dr7 = dtData.NewRow();
dr7["PK_ID"] = 0;
dr7["C_MA"] = "T7";
dr7["C_MOTA"] = "DT.Tổng chi phí quản lí phân bổ";
dr7["GroupCode"] = "";
dr7["C_Value"] = TextUtils.ToDecimal(dtFCM.Rows[0]["TotalPB"]);
if (columns.Contains("Total_NamTruoc"))
{
dr7["Total_NamTruoc"] = 0;
}
dtData.Rows.Add(dr7);
DataRow dr8 = dtData.NewRow();
dr8["PK_ID"] = 0;
dr8["C_MA"] = "T8";
dr8["C_MOTA"] = "DT.Chi phí bổ xung";
dr8["GroupCode"] = "";
dr8["C_Value"] = TextUtils.ToDecimal(dtFCM.Rows[0]["TotalBX"]);
if (columns.Contains("Total_NamTruoc"))
{
dr8["Total_NamTruoc"] = 0;
}
dtData.Rows.Add(dr8);
DataRow dr9 = dtData.NewRow();
dr9["PK_ID"] = 0;
dr9["C_MA"] = "T9";
dr9["C_MOTA"] = "DT.Loi Nhuan";
dr9["GroupCode"] = "";
dr9["C_Value"] = TextUtils.ToDecimal(dtFCM.Rows[0]["TotalProfit"]);
if (columns.Contains("Total_NamTruoc"))
{
dr9["Total_NamTruoc"] = 0;
}
dtData.Rows.Add(dr9);
#endregion
}
DataRow[] drsTong = dtData.Select("C_MA like 'T%'", "C_MA ASC");
#region Update Tổng
foreach (DataRow r in drsTong)
{
foreach (DataRow row in dtMonth.Rows)
{
string p = TextUtils.ToString(row["C_Month"]);
decimal totalTrienKhai = 0;
decimal totalPB = 0;
decimal totalReal = 0;
if (TextUtils.ToString(r["C_MA"]) == "T4")
{
string sql1 = "SELECT sum([C_PSNO]) FROM [V_XNTC_REPORT_ALL]"
+ " where [C_DTCP_MA] like '%" + fcmCode + "%' "
+ " and (FK_TKCO like '511%')"
//+ " and [C_KMP_MA] in ('C13','C52','C03','C38','C39','C40','C43','C04','C41','C42','C46','C05','C44','C45')"
+ " and [C_Year] = " + year
+ " and [C_Month] = " + p;
string sql2 = "SELECT sum([C_PSNO]) FROM [V_XNTC_REPORT_ALL]"
+ " where [C_DTCP_MA] like '%" + fcmCode + "%' "
+ " and (FK_TKNO like '521%')"
//+ " and [C_KMP_MA] in ('C13','C52','C03','C38','C39','C40','C43','C04','C41','C42','C46','C05','C44','C45')"
+ " and [C_Year] = " + year
+ " and [C_Month] = " + p;
r["T_" + p] = totalReal = TextUtils.ToDecimal(LibIE.ExcuteScalar(sql1)) - TextUtils.ToDecimal(LibIE.ExcuteScalar(sql2));
}
else if (TextUtils.ToString(r["C_MA"]) == "T5")
{
string sql1 = "SELECT sum([C_PSNO]) FROM [V_XNTC_REPORT_ALL]"
+ " where [C_DTCP_MA] like '%" + fcmCode + "%' "
+ " and (FK_TKNO like '154%' or FK_TKNO like '6321%' or FK_TKNO like '642%' or FK_TKNO like '521%' or FK_TKNO like '635%')"
+ " and [C_KMP_MA] in ('C13','C52','C03','C38','C39','C40','C43','C04','C41','C42','C46','C05','C44','C45')"
+ " and [C_Year] = " + year
+ " and [C_Month] = " + p;
r["T_" + p] = totalTrienKhai = TextUtils.ToDecimal(LibIE.ExcuteScalar(sql1));
}
else if (TextUtils.ToString(r["C_MA"]) == "T7")
{
string sql1 = "SELECT sum([C_PSNO]) FROM [V_XNTC_REPORT_ALL]"
+ " where [C_DTCP_MA] like '%" + fcmCode + "%' "
+ " and (FK_TKNO like '154%' or FK_TKNO like '6321%' or FK_TKNO like '642%' or FK_TKNO like '521%' or FK_TKNO like '635%')"
+ " and [C_KMP_MA] in ('C09P01','C09P03','C09P05','C09P06','C09P08','C09P09','C09P13','C09P14','C09P16','C09P20','C09P23'"
+ ",'C07','C12','C01','C06','C02','C17','C16','C32','C51','C47','C48',"
+ "'C50','C54','C55','C57','C10','C23','C25','C26','C11','C19','C31','C30','C53','C29','C33')"
+ " and [C_Year] = " + year
+ " and [C_Month] = " + p;
r["T_" + p] = totalPB = TextUtils.ToDecimal(LibIE.ExcuteScalar(sql1));
}
else if (TextUtils.ToString(r["C_MA"]) == "T9")
{
string sqlReal1 = "SELECT sum([C_PSNO]) FROM [V_XNTC_REPORT_ALL]"
+ " where [C_DTCP_MA] like '%" + fcmCode + "%' "
+ " and (FK_TKCO like '511%')"
//+ " and [C_KMP_MA] in ('C13','C52','C03','C38','C39','C40','C43','C04','C41','C42','C46','C05','C44','C45')"
+ " and [C_Year] = " + year
+ " and [C_Month] = " + p;
string sqlReal2 = "SELECT sum([C_PSNO]) FROM [V_XNTC_REPORT_ALL]"
+ " where [C_DTCP_MA] like '%" + fcmCode + "%' "
+ " and (FK_TKNO like '521%')"
//+ " and [C_KMP_MA] in ('C13','C52','C03','C38','C39','C40','C43','C04','C41','C42','C46','C05','C44','C45')"
+ " and [C_Year] = " + year
+ " and [C_Month] = " + p;
string sqlTrienKhai = "SELECT sum([C_PSNO]) FROM [V_XNTC_REPORT_ALL]"
+ " where [C_DTCP_MA] like '%" + fcmCode + "%' "
+ " and (FK_TKNO like '154%' or FK_TKNO like '6321%' or FK_TKNO like '642%' or FK_TKNO like '521%' or FK_TKNO like '635%')"
+ " and [C_KMP_MA] in ('C13','C52','C03','C38','C39','C40','C43','C04','C41','C42','C46','C05','C44','C45')"
+ " and [C_Year] = " + year
+ " and [C_Month] = " + p;
string sqlPB = "SELECT sum([C_PSNO]) FROM [V_XNTC_REPORT_ALL]"
+ " where [C_DTCP_MA] like '%" + fcmCode + "%' "
+ " and (FK_TKNO like '154%' or FK_TKNO like '6321%' or FK_TKNO like '642%' or FK_TKNO like '521%' or FK_TKNO like '635%')"
+ " and [C_KMP_MA] in ('C09P01','C09P03','C09P05','C09P06','C09P08','C09P09','C09P13','C09P14','C09P16','C09P20','C09P23'"
+ ",'C07','C12','C01','C06','C02','C17','C16','C32','C51','C47','C48',"
+ "'C50','C54','C55','C57','C10','C23','C25','C26','C11','C19','C31','C30','C53','C29','C33')"
+ " and [C_Year] = " + year
+ " and [C_Month] = " + p;
string sqlNhanCong = "SELECT sum([C_PSNO]) FROM [V_XNTC_REPORT_ALL]"
+ " where [C_DTCP_MA] like '%" + fcmCode + "%' "
+ " and (FK_TKNO like '154%' or FK_TKNO like '6321%' or FK_TKNO like '642%' or FK_TKNO like '521%' or FK_TKNO like '635%')"
+ " and [C_KMP_MA] in ('C09','C09P01','C09P07','C09P10','C09P11','C09P12','C09P17','C15','C34','C35','C36')"
+ " and [C_Year] = " + year
+ " and [C_Month] = " + p;
string sqlVT = "SELECT sum([C_PSNO]) FROM [V_XNTC_REPORT_ALL]"
+ " where [C_DTCP_MA] like '%" + fcmCode + "%' "
+ " and (FK_TKNO like '154%' or FK_TKNO like '6321%' or FK_TKNO like '642%' or FK_TKNO like '521%' or FK_TKNO like '635%')"
+ " and [C_KMP_MA] in ('C27','C20','C24','C22')"
+ " and [C_Year] = " + year
+ " and [C_Month] = " + p;
decimal totalLoiNhuan = TextUtils.ToDecimal(LibIE.ExcuteScalar(sqlReal1)) - TextUtils.ToDecimal(LibIE.ExcuteScalar(sqlReal2)) - TextUtils.ToDecimal(LibIE.ExcuteScalar(sqlNhanCong))
- TextUtils.ToDecimal(LibIE.ExcuteScalar(sqlPB)) - TextUtils.ToDecimal(LibIE.ExcuteScalar(sqlTrienKhai)) - 0 - TextUtils.ToDecimal(LibIE.ExcuteScalar(sqlVT));
r["T_" + p] = totalLoiNhuan;
}
else
{
r["T_" + p] = 0;
}
}
}
#endregion
dtData.Columns.Add("LuyKe", typeof(decimal), exp);
dtData.Columns.Add("ChenhLech", typeof(decimal), "C_Value - (" + exp + ")");
dtData.Columns["PK_ID"].Caption = "PK_ID";
dtData.Columns["C_MA"].Caption = "Mã";
dtData.Columns["C_MOTA"].Caption = "Tên";
dtData.Columns["GroupCode"].Caption = "Nhóm";
dtData.Columns["C_Value"].Caption = "Tổng tiền";
dtData.Columns["Total_NamTruoc"].Caption = "Năm trước";
dtData.Columns["LuyKe"].Caption = "Lũy kế";
dtData.Columns["ChenhLech"].Caption = "Chênh lệch (DK-TT)";
grvData.PopulateColumns(dtData);
grdData.DataSource = dtData;
grvData.Columns[0].Visible = false;
//grvData.Columns[3].GroupIndex = 0;
grvData.Columns[3].Visible = false;
grvData.Columns[4].Visible = false;
for (int i = 5; i < dtData.Columns.Count; i++)
{
grvData.Columns[i].DisplayFormat.FormatType = FormatType.Numeric;
grvData.Columns[i].DisplayFormat.FormatString = "n0";
}
grvData.BestFitColumns();
grvData.ExpandAllGroups();
grvData.Columns[1].Fixed = FixedStyle.Left;
grvData.Columns[2].Fixed = FixedStyle.Left;
grvData.Columns[3].Fixed = FixedStyle.Left;
grvData.Columns[4].Fixed = FixedStyle.Left;
grvData.Columns["LuyKe"].AppearanceCell.BackColor = Color.Yellow;
grvData.Columns["ChenhLech"].AppearanceCell.BackColor = Color.YellowGreen;
}
public GridBuilderModel GridBuilder(GridBuilderModel model, bool isDeser)
{
if (model != null && !string.IsNullOrEmpty(model.StringXml))
{
var columnMode = model.StringXml;
XmlDocument doc = new XmlDocument();
if (!isDeser)
{
var splitStringXml = model.StringXml.Split('|');
columnMode = splitStringXml[0].ToString();
if (splitStringXml.Length > 1)
{
var strPageSize = splitStringXml[1].ToString();
var pageSize = 0;
int.TryParse(strPageSize, out pageSize);
model.PageSize = pageSize;
}
//Chuyển xml thành json để xử lý ajax
doc.LoadXml(columnMode);
model.StringXml = JsonConvert.SerializeXmlNode(doc);
}
else
{
doc = JsonConvert.DeserializeXmlNode(columnMode);
}
//Chuyển xml thành data table để xử lý hiển thị colum trên popup
DataSet ds = new DataSet();
var reader = new XmlNodeReader(doc);
ds.ReadXml(reader);
if (ds.Tables.Count > 0)
{
var table = ds.Tables[0];
var listValueFields = new List <string>();
var listLockeFields = new List <string>();
var listGroupFields = new List <string>();
var listSizeFields = new Dictionary <string, int>();
var listDisplayFields = new Dictionary <string, string>();
var listFilterFields = new Dictionary <string, bool>();
DataColumnCollection columns = table.Columns;
for (int i = 0; i < table.Rows.Count; i++)
{
if (columns.Contains("ColumnName"))
{
var colum = table.Rows[i]["ColumnName"].ToString();
if (columns.Contains("Width"))
{
var value = table.Rows[i]["Width"].ToString();
if (!string.IsNullOrEmpty(value))
{
var width = int.Parse(value);
listSizeFields.Add(colum, width);
}
}
if (columns.Contains("Locke"))
{
var locke = bool.Parse(table.Rows[i]["Locke"].ToString());
if (locke)
{
listLockeFields.Add(colum);
}
}
if (columns.Contains("Group"))
{
var group = bool.Parse(table.Rows[i]["Group"].ToString());
if (group)
{
listGroupFields.Add(colum);
}
}
if (columns.Contains("Filter"))
{
var filter = bool.Parse(table.Rows[i]["Filter"].ToString());
listFilterFields.Add(colum, filter);
}
listValueFields.Add(colum);
listDisplayFields.Add(colum, colum.TranslateString());
}
}
model.ValueFields = listValueFields.ToArray();
model.SizeFields = listSizeFields;
model.DisplayFields = listDisplayFields;
model.LockedFields = listLockeFields.ToArray();
model.GroupFields = listGroupFields.ToArray();
model.DisplayFields = listDisplayFields;
model.FilterFields = listFilterFields;
}
}
return(model);
}
/// <summary>
/// DataTable导出到Excel的MemoryStream
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="strHeaderText">表头文本</param>
/// <param name="headers">需要导出的列的列头</param>
/// <param name="cellKes">需要导出的对应的列字段</param>
/// <returns></returns>
public static MemoryStream Export(DataTable dtSource, string strHeaderText, string[] headers, string[] cellKes)
{
// excel
HSSFWorkbook workbook = new HSSFWorkbook();
// 创建一个sheet页，已strHeaderText命名
ISheet sheet = workbook.CreateSheet(strHeaderText);
#region 右击文件 属性信息
{
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "NPOI";
workbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
//si.Author = "文件作者信息"; //填加xls文件作者信息
//si.ApplicationName = "创建程序信息"; //填加xls文件创建程序信息
//si.LastAuthor = "最后保存者信息"; //填加xls文件最后保存者信息
//si.Comments = "作者信息"; //填加xls文件作者信息
//si.Title = "标题信息"; //填加xls文件标题信息
//si.Subject = "主题信息";//填加文件主题信息
si.CreateDateTime = DateTime.Now;
workbook.SummaryInformation = si;
}
#endregion
// 日期的样式
ICellStyle dateStyle = workbook.CreateCellStyle();
// 日期的格式化
IDataFormat format = workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
// 字体样式
IFont datafont = workbook.CreateFont();
// 字体大小
datafont.FontHeightInPoints = 11;
dateStyle.SetFont(datafont);
// 边框
dateStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
dateStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
dateStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
dateStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
// 其他数据的样式
ICellStyle cellStyle = workbook.CreateCellStyle();
// 字体样式
IFont cellfont = workbook.CreateFont();
// 字体大小
cellfont.FontHeightInPoints = 11;
cellStyle.SetFont(cellfont);
// 边框
cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
// 总的列数
int colNum = headers.Length;
// 每个列的宽度
int[] arrColWidth = new int[colNum];
// 初始化列的宽度为列头的长度，已需要显示的列头的名字长度计算
for (int i = 0; i < headers.Length; i++)
{
arrColWidth[i] = Encoding.GetEncoding(936).GetBytes(headers[i]).Length * 3;
}
// 循环数据，取每列数据最宽的作为该列的宽度
for (int i = 0; i < dtSource.Rows.Count; i++)
{
for (int j = 0; j < cellKes.Length; j++)
{
int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][cellKes[j]].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
}
// 记录生成的行数
int rowIndex = 0;
// DataTable中的列信息
DataColumnCollection columns = dtSource.Columns;
// 循环所有的行，向sheet页中添加数据
foreach (DataRow row in dtSource.Rows)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
// 如果不是第一行，则创建一个新的sheet页
if (rowIndex != 0)
{
sheet = workbook.CreateSheet();
}
#region 表头及样式
{
// 在当前sheet页上创建第一行
IRow headerRow = sheet.CreateRow(0);
// 该行的高度
headerRow.HeightInPoints = 50;
// 设置第一列的值
headerRow.CreateCell(0).SetCellValue(strHeaderText);
// 设置列的样式
ICellStyle headStyle = workbook.CreateCellStyle();
// 内容居中显示
headStyle.Alignment = HorizontalAlignment.Center;
// 字体样式
IFont font = workbook.CreateFont();
// 字体大小
font.FontHeightInPoints = 20;
// 粗体显示
font.Boldweight = 700;
// 字体颜色
font.Color = NPOI.HSSF.Util.HSSFColor.Blue.Index;
headStyle.SetFont(font);
// 边框
headStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
headStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
headStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
headStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
// 设置单元格的样式
headerRow.GetCell(0).CellStyle = headStyle;
// 设置该行每个单元格的样式
for (int i = 1; i < colNum; i++)
{
headerRow.CreateCell(i);
headerRow.GetCell(i).CellStyle = headStyle;
}
// 合并单元格
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, colNum - 1));
}
#endregion
#region 列头及样式
{
// 创建第二行
IRow headerRow = sheet.CreateRow(1);
// 该行的高度
headerRow.HeightInPoints = 28;
// 列的样式
ICellStyle headStyle = workbook.CreateCellStyle();
// 单元格内容居中显示
headStyle.Alignment = HorizontalAlignment.Center;
// 字体样式
IFont font = workbook.CreateFont();
// 字体大小
font.FontHeightInPoints = 12;
// 粗体
font.Boldweight = 700;
// 字体颜色
font.Color = NPOI.HSSF.Util.HSSFColor.Black.Index;
headStyle.SetFont(font);
// 边框
headStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
headStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
headStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
headStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
// 设置每列的样式和值
for (int i = 0; i < headers.Length; i++)
{
headerRow.CreateCell(i).SetCellValue(headers[i]);
headerRow.GetCell(i).CellStyle = headStyle;
//设置列宽
sheet.SetColumnWidth(i, (arrColWidth[i] + 1) * 256);
}
}
#endregion
rowIndex = 2;
}
#endregion
#region 填充内容
// 创建新的一行
IRow dataRow = sheet.CreateRow(rowIndex);
// 该行的高度
dataRow.HeightInPoints = 23;
// 循环需要写入的每列数据
for (int i = 0; i < cellKes.Length; i++)
{
// 创建列
ICell newCell = dataRow.CreateCell(i);
// 获取DataTable中该列对象
DataColumn column = columns[cellKes[i]];
// 该列的值
string drValue = row[column].ToString();
// 根据值得类型分别处理之后赋值
switch (column.DataType.ToString())
{
case "System.String": //字符串类型
newCell.SetCellValue(drValue);
newCell.CellStyle = cellStyle;
break;
case "System.DateTime": //日期类型
DateTime dateV;
DateTime.TryParse(drValue, out dateV);
newCell.SetCellValue(dateV);
newCell.CellStyle = dateStyle; //格式化显示
break;
case "System.Boolean": //布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell.SetCellValue(boolV);
newCell.CellStyle = cellStyle;
break;
case "System.Int16": //整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
newCell.CellStyle = cellStyle;
break;
case "System.Decimal": //浮点型
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
newCell.CellStyle = cellStyle;
break;
case "System.DBNull": //空值处理
newCell.SetCellValue("");
newCell.CellStyle = cellStyle;
break;
default:
newCell.SetCellValue("");
newCell.CellStyle = cellStyle;
break;
}
}
#endregion
rowIndex++;
}
MemoryStream ms = new MemoryStream();
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
return(ms);
}
public static object GetValueIfContains(DataColumnCollection columns, DataRow dr, string col)
{
return(columns.Contains(col) ? dr[col] : null);
}
private DataColumn[] FilterColumns(DataTable sourceTable, string[] hiddenColumnNames, DataColumnCollection destinationColumns)
{
int columnCount = 0;
foreach (DataColumn sourceColumn in sourceTable.Columns)
{
if (IncludeThisColumn(sourceColumn, hiddenColumnNames))
{
columnCount++;
}
}
if (columnCount == 0)
{
throw ADP.NoColumns();
}
int currentColumn = 0;
DataColumn[] filteredSourceColumns = new DataColumn[columnCount];
foreach (DataColumn sourceColumn in sourceTable.Columns)
{
if (IncludeThisColumn(sourceColumn, hiddenColumnNames))
{
DataColumn newDestinationColumn = new(sourceColumn.ColumnName, sourceColumn.DataType);
destinationColumns.Add(newDestinationColumn);
filteredSourceColumns[currentColumn] = sourceColumn;
currentColumn++;
}
}
return(filteredSourceColumns);
}
private void LoadReport()
{
Int32 bonderId = (Int32)Session[AppConstants.SearchBonderID];
System.DateTime searchDateFrom = (System.DateTime)Session[AppConstants.SearchFromDate];
System.DateTime searchDateTo = (System.DateTime)Session[AppConstants.SearchToDate];
List <InBondExBondMaterial> reportData = new List <InBondExBondMaterial>();
string queryStr1 = "SELECT"
+ " BONDER.BONDERSLNO AS BonderID,"
+ " BONDER.BONDERNAME AS BonderName,"
+ " BONDER.BONDLICENSENO AS BonderLCNo,"
+ " BONDER.ADDRESS AS BonderAddress,"
+ " INBOND.BOENUMBER AS BOE,"
+ " INBOND.LCDATE AS ImportDate,"
+ " INBOND.LCNUMBER AS LC,"
+ " INBOND.CREATEDDATE AS InBondDate,"
+ " INBONDRAWMATERIAL.PRODUCTQUANTITY AS InBondQuantity,"
+ " INBONDRAWMATERIAL.QUANTITYUNIT AS InBondQuantityUnit,"
+ " INBONDRAWMATERIAL.PRODUCTCOST AS InBondValue,"
+ " INBONDRAWMATERIAL.COSTUNIT AS InBondValueUnit,"
+ " MATERIALS.MHSCODE AS HSCode,"
+ " MATERIALS.MATERIALNAME AS RawMaterialName,"
+ " MATERIALS.MSLNO AS RAWMATERIALCODE"
+ " FROM INBONDRAWMATERIAL"
+ " INNER JOIN INBOND ON INBOND.ID = INBONDRAWMATERIAL.INBONDID"
+ " INNER JOIN MATERIALS ON MATERIALS.MSLNO = INBONDRAWMATERIAL.RAWMATERIALCODE"
+ " INNER JOIN BONDER ON BONDER.BONDERSLNO = INBOND.BONDERID"
+ " WHERE INBOND.BONDERID = " + bonderId + " and INBOND.CREATEDDATE BETWEEN TO_DATE('" + searchDateFrom + "', '" + AppConstants.DATE_FORMATE + "')"
+ " AND TO_DATE('" + searchDateTo + "', '" + AppConstants.DATE_FORMATE + "')"
+ " ORDER BY MATERIALS.MATERIALNAME, INBOND.CREATEDDATE";
reportData = db.Database.SqlQuery <InBondExBondMaterial>(queryStr1).ToList();
List <InBondExBondMaterial> finalData = new List <InBondExBondMaterial>();
//Update data based on response
for (int i = 0; i < reportData.Count(); i++)
{
InBondExBondMaterial ibebm = reportData.ElementAt(i);
//Adding fromdate and todate value to show in report
ibebm.DateFrom = searchDateFrom;
ibebm.DateTo = searchDateTo;
finalData.Add(ibebm);
List <InBondExBondMaterial> exbondList = new List <InBondExBondMaterial>();
if (i == reportData.Count - 1)
{
//Last element. add all exbond from this to SearchToDate
exbondList = exbondListByRawMaterialInDate(ibebm, ibebm.DateTo, false);
}
else
{ //Get the next element and check if it is same material
InBondExBondMaterial ibNext = reportData.ElementAt(i + 1);
if (ibNext.RAWMATERIALCODE.Equals(ibebm.RAWMATERIALCODE))
{
exbondList = exbondListByRawMaterialInDate(ibebm, ibNext.InBondDate, true);
}
else
{
//Last element of this kind. add all exbond from this to SearchToDate
exbondList = exbondListByRawMaterialInDate(ibebm, ibebm.DateTo, false);
}
}
finalData.AddRange(exbondList);
}
//return finalData;
//Clear session data
//Session[AppConstants.SearchBonderID] = null;
//Session[AppConstants.SearchFromDate] = null;
//Session[AppConstants.SearchToDate] = null;
//Load data in report
//Prepare report
ReportDocument rd = new ReportDocument();
rd.Load(Path.Combine(Server.MapPath("~/Report/Crystal"), "InBondExBondRawMaterialStatus_2.rpt"));
//Set datasource
InBondExBondRawMaterial_01 ibrd = new InBondExBondRawMaterial_01();
DataTable inbondTb = ibrd.Tables[ibrd.Tables.IndexOf("InBondExBondRawMaterial")];
DataRow dr;
foreach (object ib in finalData)
{
dr = inbondTb.NewRow();
DataColumnCollection dcc = inbondTb.Columns;
foreach (DataColumn dc in dcc)
{
Type tp = ib.GetType();
PropertyInfo pif = tp.GetProperty(dc.ColumnName);
var value = pif.GetValue(ib);
dr[dc.ColumnName] = ib.GetType().GetProperty(dc.ColumnName).GetValue(ib, null);
}
inbondTb.Rows.Add(dr);
}
rd.SetDataSource(inbondTb);
InBondExBondRptVwr.ReportSource = rd;
InBondExBondRptVwr.DataBind();
}
/// <summary>
/// Read CSV file and gather the curves as requested by client.
/// </summary>
/// <param name="message">String received from client</param>
/// <returns>Returns either Curve details</returns>
public string ReadCSV(string message)
{
string commaSeperatedValues = "";
int found = 0, firstindex = 0, diff = 0;;
Console.WriteLine("Message received is {0}", message);
string[] RequestedCurves = message.Split(',');
int readcolumns = 0;
DataTable csvdatatable = new DataTable();
using (var reader = new StreamReader(@"D:\testfile.txt"))
{
while (!reader.EndOfStream)
{
var line = reader.ReadLine();
string[] values = line.Split(';');
string[] commaseparated = values[0].Split(',');
if (readcolumns == 0)
{
readcolumns = 1;
for (int i = 0; i < commaseparated.Length; i++)
{
if (commaseparated[i] == "index")
{
commaseparated[i] = commaseparated[i].Replace(" ", "");
csvdatatable.Columns.Add(Convert.ToString(commaseparated[i]), typeof(string));
}
else
{
commaseparated[i] = commaseparated[i].Replace(" ", "");
csvdatatable.Columns.Add(commaseparated[i], typeof(string));
}
}
}
else
{
DataRow dr1 = csvdatatable.NewRow();
for (int j = 0; j < commaseparated.Length; j++)
{
if (commaseparated[j] == " ")
{
dr1[j] = "null";
}
else
{
commaseparated[j] = commaseparated[j].Replace(" ", "");
dr1[j] = commaseparated[j];
}
}
csvdatatable.Rows.Add(dr1);
}
}
firstindex = Convert.ToInt32(csvdatatable.Rows[0][0]);
diff = Convert.ToInt32(csvdatatable.Rows[1][0]) - Convert.ToInt32(csvdatatable.Rows[0][0]);
}
DataColumnCollection columns = csvdatatable.Columns;
for (int j = 0; j < RequestedCurves.Length; j++)
{
if (found == 1)
{
commaSeperatedValues = commaSeperatedValues + ";";
}
if (columns.Contains(RequestedCurves[j]))
{
found = 1;
commaSeperatedValues = commaSeperatedValues + firstindex + "*" + diff + '@' + RequestedCurves[j] + ":";
var SelectedValues = csvdatatable.AsEnumerable().Select(s => s.Field <string>(RequestedCurves[j])).ToArray();
commaSeperatedValues = commaSeperatedValues + string.Join(",", SelectedValues);
}
}
if (found == 0)
{
commaSeperatedValues = "NOTFOUND";
}
return(commaSeperatedValues);
}
internal WorkSheet()
{
_Columns = new DataColumnCollection();
_Rows = new DataRowCollection();
}
/// <summary>
/// 根据SQL查询结果 生成数据创建脚本
/// </summary>
/// <returns></returns>
public string CreateTabScriptBySQL(string dbname, string strSql)
{
dbobj.DbConnectStr = _dbconnectStr;
string PKfild = ""; //主键字段
bool IsIden = false; //是否是标识字段
string tablename = "TableName";
StringPlus strclass = new StringPlus();
#region 查询表名
int ns = strSql.IndexOf(" from ");
if (ns > 0)
{
string sqltemp = strSql.Substring(ns + 5).Trim();
int ns2 = sqltemp.IndexOf(" ");
if (sqltemp.Length > 0)
{
if (ns2 > 0)
{
tablename = sqltemp.Substring(0, ns2).Trim();
}
else
{
tablename = sqltemp.Substring(0).Trim();
}
}
}
tablename = tablename.Replace("[", "").Replace("]", "");
#endregion
#region 数据脚本
//获取数据
DataTable dtdata = dbobj.GetTabDataBySQL(dbname, strSql);
if (dtdata != null)
{
DataColumnCollection dtcols = dtdata.Columns;
foreach (DataRow row in dtdata.Rows)//循环表数据
{
StringPlus strfild = new StringPlus();
StringPlus strdata = new StringPlus();
foreach (DataColumn col in dtcols)//循环一行数据的各个字段
{
string colname = col.ColumnName;
string coltype = col.DataType.Name;
if (col.AutoIncrement)
{
IsIden = true;
}
string strval = "";
switch (coltype.ToLower())
{
case "binary":
case "byte[]":
case "blob":
{
byte[] bys = (byte[])row[colname];
strval = CodeCommon.ToHexString(bys);
}
break;
case "bit":
case "boolean":
{
strval = (row[colname].ToString().ToLower() == "true") ? "1" : "0";
}
break;
default:
strval = row[colname].ToString().Trim();
break;
}
if (strval != "")
{
if (CodeCommon.IsAddMark(coltype))
{
strdata.Append("'" + strval + "',");
}
else
{
strdata.Append(strval + ",");
}
strfild.Append("" + colname + ",");
}
}
strfild.DelLastComma();
strdata.DelLastComma();
//导出数据INSERT语句
strclass.Append("INSERT " + tablename + " (");
strclass.Append(strfild.Value);
strclass.Append(") VALUES ( ");
strclass.Append(strdata.Value);//数据值
strclass.AppendLine(")");
}
}
//StringPlus strclass0 = new StringPlus();
//if (IsIden)
//{
// strclass0.AppendLine("SET IDENTITY_INSERT [" + tablename + "] ON");
// strclass0.AppendLine("");
//}
//strclass0.AppendLine(strclass.Value);
//if (IsIden)
//{
// strclass0.AppendLine("");
// strclass0.AppendLine("SET IDENTITY_INSERT [" + tablename + "] OFF");
//}
#endregion
return(strclass.Value);
}
/// <inheritdoc/>
public IEnumerable<int> GetSelectedRowIndicesFromTo(int startIndex, int maxIndex, DataColumnCollection table, int totalRowCount)
{
int start = Math.Max(startIndex, _firstRowIndexInclusive >= 0 ? _firstRowIndexInclusive : _firstRowIndexInclusive + totalRowCount);
int endInclusive = Math.Min(Math.Min(maxIndex - 1, totalRowCount - 1), _lastRowIndexInclusive >= 0 ? _lastRowIndexInclusive : _lastRowIndexInclusive + totalRowCount);
for (int r = start; r <= endInclusive; ++r)
yield return r;
}
/// <summary>
/// 从DataTable的Columns构造
/// </summary>
/// <param name="columns"></param>
public SOARolePropertyDefinitionCollection(DataColumnCollection columns)
: base(StringComparer.OrdinalIgnoreCase)
{
this.FromDataColumns(columns);
}
/// <inheritdoc/>
public IEnumerable<int> GetSelectedRowIndicesFromTo(int startIndex, int maxIndex, DataColumnCollection table, int totalRowCount)
{
IEnumerator<int>[] _enumerators = new IEnumerator<int>[_rowSelections.Count];
int maxCurrentIndex = 0;
int currentIndex0 = 0;
bool isUniform = false;
for (int i = 0; i < _rowSelections.Count; ++i)
{
_enumerators[i] = _rowSelections[i].GetSelectedRowIndicesFromTo(startIndex, maxIndex, table, totalRowCount).GetEnumerator();
}
for (;;)
{
for (int i = 0; i < _rowSelections.Count; ++i)
{
if (!_enumerators[i].MoveNext())
goto BreakEnumeration; // if one enumerator has no more items, we can end this enumeration
if (i == 0)
{
currentIndex0 = maxCurrentIndex = _enumerators[i].Current;
isUniform = true;
}
else
{
isUniform &= (_enumerators[i].Current == currentIndex0);
maxCurrentIndex = Math.Max(_enumerators[i].Current, maxCurrentIndex);
}
}
if (!(maxCurrentIndex <= maxIndex))
goto BreakEnumeration; // end of range is reached
if (isUniform) // all enumerators have the same current value -> thus we can yield this value;
{
yield return currentIndex0;
continue; // and we try to move all enumerators to the next item.
}
else // not uniform
{
for (int i = 0; i < _rowSelections.Count; ++i)
{
_enumerators[i].Dispose(); // dispose old enumerators
_enumerators[i] = _rowSelections[i].GetSelectedRowIndicesFromTo(maxCurrentIndex, maxIndex, table, totalRowCount).GetEnumerator(); // use new enumerators which start at maxCurrentItem
}
}
}
BreakEnumeration:
for (int i = 0; i < _rowSelections.Count; ++i)
{
_enumerators[i].Dispose();
}
}
/// <summary>
/// Updates the Expression-Object with the columns that exist inside the features that belong to this category. They are used for calculating the expression.
/// </summary>
/// <param name="columns">Columns that should be updated.</param>
/// <returns>False if columns were not set.</returns>
public bool UpdateExpressionColumns(DataColumnCollection columns)
{
return(_exp.UpdateFields(columns));
}
/// <summary>
/// Creates the columns when AutoGenerateColumns=false (takes care of Images, etc...)
/// </summary>
/// <param name="dcol">DataColumn collection</param>
private void CreateColumns(DataColumnCollection dcol)
{
this.Columns.Clear();
foreach (DataColumn dc in dcol)
{
DataGridColumn dgc = null;
switch (dc.DataType.Name)
{
case "Boolean":
DataGridCheckBoxColumn checkBoxColumn = new DataGridCheckBoxColumn();
checkBoxColumn.Binding = new Binding(dc.ColumnName);
dgc = checkBoxColumn;
break;
case "Image":
DataGridTemplateColumn templateColumn = new DataGridTemplateColumn();
// Build template in memory
StringBuilder CellTemp = new StringBuilder();
CellTemp.Append("<DataTemplate ");
CellTemp.Append("xmlns='http://schemas.microsoft.com/winfx/");
CellTemp.Append("2006/xaml/presentation' ");
CellTemp.Append("xmlns:x='http://schemas.microsoft.com/winfx/2006/xaml' ");
CellTemp.Append("xmlns:local='clr-namespace:BindableDataGrid;assembly=BindableDataGrid'>");
CellTemp.Append("<Image Source='{Binding " + dc.ColumnName + ".Source }' ></Image>");
CellTemp.Append("</DataTemplate>");
templateColumn.CellTemplate = (DataTemplate)XamlReader.Load(CellTemp.ToString());
// Set editing template the same as regular one (will not be editable)
templateColumn.CellEditingTemplate = (DataTemplate)XamlReader.Load(CellTemp.ToString());
dgc = templateColumn;
break;
//case "String":
//case "Int32":
//case "DateTime":
default: // Treat everything else as a string
DataGridTextColumn textColumn = new DataGridTextColumn();
textColumn.Binding = new Binding(dc.ColumnName);
dgc = textColumn;
break;
}
if (dgc != null)
{
dgc.Header = dc.Caption;
dgc.IsReadOnly = dc.ReadOnly;
dgc.CanUserResize = dc.AllowResize;
dgc.CanUserSort = dc.AllowSort;
dgc.CanUserReorder = dc.AllowReorder;
this.Columns.Add(dgc);
}
}
}
private static string[] setColumnNamesForDataArray(string[] myColumnNamesAndVals, DataColumnCollection subtractorColumns)
{
for (int i = 0; i < subtractorColumns.Count * 2; i = i + 2) // Set the Column names for each column (but not their values)
{
myColumnNamesAndVals[i] = subtractorColumns[i / 2].ColumnName;
}
return myColumnNamesAndVals;
}
