/// <summary>
/// Returns DataRow object for this editor.
/// </summary>
protected DataRow GetColumnRow(int rowIndex)
{
// Check if have enougth data for initial processing
if (DataGridView == null ||
DataGridView.Rows == null ||
rowIndex < 0 ||
rowIndex >= DataGridView.RowCount)
{
return(null);
}
// Extract DataGridViewRow, DataRowView and DataRow
DataGridViewRow gridRow = DataGridView.Rows[rowIndex];
DataRowView rowView = gridRow != null ? gridRow.DataBoundItem as DataRowView : null;
DataRow row = rowView != null ? rowView.Row : null;
// Check results for emptines
if (row == null || row.Table == null || row.Table.Columns == null)
{
return(null);
}
// Check for available columns
DataColumnCollection columns = row.Table.Columns;
if (!columns.Contains(Column.MySqlType) ||
!columns.Contains(Column.Unsigned) ||
!columns.Contains(Column.Zerofill) ||
!columns.Contains(Column.Binary) ||
!columns.Contains(Column.Ascii) ||
!columns.Contains(Column.Unicode))
{
Debug.Fail("Wrong table structure!");
return(null);
}
return(row);
}
public Tarjeta initialize(DataRow _dr)
{
dr = _dr;
DataColumnCollection dcc = dr.Table.Columns;
if (dcc.Contains("numero"))
{
numero = (dr["numero"] == DBNull.Value) ? null : (long?)Convert.ToInt64(dr["numero"]);
}
if (dcc.Contains("fecha_emision"))
{
fecha_emision = (dr["fecha_emision"] == DBNull.Value) ? null : (DateTime?)Convert.ToDateTime(dr["fecha_emision"]);
}
if (dcc.Contains("fecha_vencimiento"))
{
fecha_vencimiento = (dr["fecha_vencimiento"] == DBNull.Value) ? null : (DateTime?)Convert.ToDateTime(dr["fecha_vencimiento"]);
}
if (dcc.Contains("cod_seguridad"))
{
cod_seguridad = (dr["cod_seguridad"] == DBNull.Value) ? null : (int?)Convert.ToInt32(dr["cod_seguridad"]);
}
if (dcc.Contains("emisor"))
{
emisor = (dr["emisor"] == DBNull.Value) ? null : (int?)Convert.ToInt32(dr["emisor"]);
}
if (dcc.Contains("cli_cod"))
{
cli_cod = (dr["cli_cod"] == DBNull.Value) ? null : (int?)Convert.ToInt32(dr["cli_cod"]);
}
numeroMostrable = this.numberVisualize;
DAOEmisor daoEmisor = new DAOEmisor();
tEmisor = daoEmisor.retrieveBy_id(emisor);
return(this);
}
/// <summary>
/// 将数据行转换为对象
/// </summary>
/// <param name="dt">要转换的数据行</param>
/// <returns></returns>
public Table_1 DataRowToModel(DataRow dr)
{
Table_1 _table_1 = null;
object _temp = null;
DataColumnCollection dcs = dr.Table.Columns;
_table_1 = new Table_1();
if (dcs.Contains("id"))
{
_table_1.ID = Convert.ToInt64(dr["ID"]);
}
if (dcs.Contains("userid"))
{
_table_1.UserID = dr["UserID"].ToString();
}
if (dcs.Contains("iden"))
{
_table_1.Iden = Convert.ToInt32(dr["Iden"]);
}
if (dcs.Contains("s1"))
{
_table_1.S1 = Convert.ToDecimal(dr["s1"]);
}
if (dcs.Contains("s2"))
{
_table_1.S2 = Convert.ToDecimal(dr["s2"]);
}
if (dcs.Contains("ssum"))
{
_temp = dr["SSum"];
if (!(_temp is DBNull))
{
_table_1.SSum = Convert.ToDecimal(_temp);
}
}
return(_table_1);
}
/// <summary>
/// 将数据行转换为对象
/// </summary>
/// <param name="dt">要转换的数据行</param>
/// <returns></returns>
public BU_AcIO DataRowToModel(DataRow dr)
{
BU_AcIO _bu_acio = null;
object _temp = null;
DataColumnCollection dcs = dr.Table.Columns;
_bu_acio = new BU_AcIO();
if (dcs.Contains("id"))
{
_bu_acio.ID = Convert.ToInt64(dr["ID"]);
}
if (dcs.Contains("userid"))
{
_bu_acio.UserID = Convert.ToInt64(dr["UserID"]);
}
if (dcs.Contains("acbookid"))
{
_bu_acio.AcBookID = Convert.ToInt64(dr["AcBookID"]);
}
if (dcs.Contains("acbookname"))
{
_bu_acio.AcBookName = dr["AcBookName"].ToString();
}
if (dcs.Contains("opway"))
{
_bu_acio.OpWay = Convert.ToInt32(dr["OpWay"]);
}
if (dcs.Contains("paytype"))
{
_bu_acio.PayType = Convert.ToInt32(dr["PayType"]);
}
if (dcs.Contains("paytype2"))
{
_bu_acio.PayType2 = dr["PayType2"].ToString();
}
if (dcs.Contains("paytype3"))
{
_bu_acio.PayType3 = dr["PayType3"].ToString();
}
if (dcs.Contains("mediid"))
{
_bu_acio.MediID = Convert.ToInt64(dr["MediID"]);
}
if (dcs.Contains("mediname"))
{
_bu_acio.MediName = dr["MediName"].ToString();
}
if (dcs.Contains("paywayid"))
{
_bu_acio.PayWayID = Convert.ToInt64(dr["PayWayID"]);
}
if (dcs.Contains("paywayname"))
{
_bu_acio.PayWayName = dr["PayWayName"].ToString();
}
if (dcs.Contains("youdebtid"))
{
_bu_acio.YouDebtID = Convert.ToInt64(dr["YouDebtID"]);
}
if (dcs.Contains("youcomname"))
{
_bu_acio.YouComName = dr["YouComName"].ToString();
}
if (dcs.Contains("youname"))
{
_bu_acio.YouName = dr["YouName"].ToString();
}
if (dcs.Contains("youwayid"))
{
_bu_acio.YouWayID = Convert.ToInt64(dr["YouWayID"]);
}
if (dcs.Contains("youwayname"))
{
_bu_acio.YouWayName = dr["YouWayName"].ToString();
}
if (dcs.Contains("youwayidnumber"))
{
_bu_acio.YouWayIDNumber = dr["YouWayIDNumber"].ToString();
}
if (dcs.Contains("paydate"))
{
_bu_acio.PayDate = Convert.ToDateTime(dr["PayDate"]);
}
if (dcs.Contains("paydatestr"))
{
_bu_acio.PayDateStr = dr["PayDateStr"].ToString();
}
if (dcs.Contains("money_old"))
{
_bu_acio.Money_Old = Convert.ToDecimal(dr["Money_Old"]);
}
if (dcs.Contains("money_added"))
{
_bu_acio.Money_Added = Convert.ToDecimal(dr["Money_Added"]);
}
if (dcs.Contains("money_percent"))
{
_bu_acio.Money_Percent = Convert.ToInt32(dr["Money_Percent"]);
}
if (dcs.Contains("setmoney"))
{
_bu_acio.SetMoney = Convert.ToDecimal(dr["SetMoney"]);
}
if (dcs.Contains("paymoney"))
{
_bu_acio.PayMoney = Convert.ToDecimal(dr["PayMoney"]);
}
if (dcs.Contains("money_result"))
{
_bu_acio.Money_Result = Convert.ToDecimal(dr["Money_Result"]);
}
if (dcs.Contains("remark"))
{
_bu_acio.Remark = dr["Remark"].ToString();
}
if (dcs.Contains("yumoney"))
{
_bu_acio.YuMoney = Convert.ToDecimal(dr["YuMoney"]);
}
if (dcs.Contains("isbreaktolinkuser"))
{
_bu_acio.IsBreakToLinkUser = Convert.ToInt32(dr["IsBreakToLinkUser"]);
}
if (dcs.Contains("isbreaktopayway"))
{
_bu_acio.IsBreakToPayWay = Convert.ToInt32(dr["IsBreakToPayWay"]);
}
if (dcs.Contains("isbaoxiao"))
{
_bu_acio.IsBaoXiao = Convert.ToInt32(dr["IsBaoXiao"]);
}
if (dcs.Contains("linkuserid"))
{
_bu_acio.LinkUserID = Convert.ToInt64(dr["LinkUserID"]);
}
if (dcs.Contains("linkusername"))
{
_bu_acio.LinkUserName = dr["LinkUserName"].ToString();
}
if (dcs.Contains("linktableid"))
{
_bu_acio.LinkTableID = Convert.ToInt64(dr["LinkTableID"]);
}
if (dcs.Contains("applytablename"))
{
_bu_acio.ApplyTableName = dr["ApplyTableName"].ToString();
}
if (dcs.Contains("applytableid"))
{
_bu_acio.ApplyTableID = Convert.ToInt64(dr["ApplyTableID"]);
}
if (dcs.Contains("adddate"))
{
_bu_acio.AddDate = Convert.ToDateTime(dr["AddDate"]);
}
return(_bu_acio);
}
private void FillObjectFromDataRow(DataRow dataRow)
{
try
{
DataColumnCollection columns = dataRow.Table.Columns;
JNPID = (long)dataRow["JNPID"];
JNPTypeID = (int)dataRow["JNPTypeID"];
JNPOptionTypeID = (int)dataRow["JNPOptionTypeID"];
if (dataRow["IsStandardJNP"] != DBNull.Value)
{
IsStandardJNP = (bool)dataRow["IsStandardJNP"];
}
if (dataRow["IsDEU"] != DBNull.Value)
{
IsStandardJNP = (bool)dataRow["IsDEU"];
}
PayPlanID = (int)dataRow["PayPlanID"];
SeriesID = (int)dataRow["SeriesID"];
if (dataRow["Grade"] != DBNull.Value)
{
Grade = (string)dataRow["Grade"];
}
if (dataRow["IsInterdisciplinary"] != DBNull.Value)
{
IsInterdisciplinary = (bool)dataRow["IsInterdisciplinary"];
}
if (dataRow["AdditionalSeriesID"] != DBNull.Value)
{
AdditionalSeriesID = (int)dataRow["AdditionalSeriesID"];
}
LowestAdvertisedGrade = (int)dataRow["LowestAdvertisedGrade"];
HighestAdvertisedGrade = (int)dataRow["HighestAdvertisedGrade"];
if (dataRow["RegionID"] != DBNull.Value)
{
RegionID = (int)dataRow["RegionID"];
}
dataRow["OrganizationCodeID"] = (int)dataRow["OrganizationCodeID"];
if (columns.Contains("OrganizationCode"))
{
if (dataRow ["OrganizationCode"] != DBNull.Value)
{
OrganizationCode = (string)dataRow["OrganizationCode"];
}
}
if (columns.Contains("OrganizationCodeName"))
{
if (dataRow["OrganizationName"] != DBNull.Value)
{
OrganizationName = (string)dataRow["OrganizationName"];
}
}
if (columns.Contains("OldOrganizationCode"))
{
if (dataRow["OldOrganizationCode"] != DBNull.Value)
{
OldOrganizationCode = dataRow["OldOrganizationCode"].ToString();
}
}
if (dataRow["JNPTitle"] != DBNull.Value)
{
JNPTitle = (string)dataRow["JNPTitle"];
}
if (dataRow["JNPTitleCombined"] != DBNull.Value)
{
JNPTitleCombined = (string)dataRow["JNPTitleCombined"];
}
if (dataRow["FullPDID"] != DBNull.Value)
{
FullPDID = (long)dataRow["FullPDID"];
}
if (dataRow["AdditionalPDID"] != DBNull.Value)
{
AdditionalPDID = (long)dataRow["AdditionalPDID"];
}
if (dataRow["JAID"] != DBNull.Value)
{
JAID = (long)dataRow["JAID"];
}
if (dataRow["CRID"] != DBNull.Value)
{
CRID = (long)dataRow["CRID"];
}
if (dataRow["JQID"] != DBNull.Value)
{
JQID = (long)dataRow["JQID"];
}
if (dataRow["CreatedByID"] != DBNull.Value)
{
CreatedByID = (int)dataRow["CreatedByID"];
}
if (dataRow["CreatedBy"] != DBNull.Value)
{
CreatedBy = (string)dataRow["CreatedBy"];
}
if (dataRow["CreateDate"] != DBNull.Value)
{
CreateDate = (DateTime)dataRow["CreateDate"];
}
//if (dataRow["CreateDateShort"] != DBNull.Value) CreateDateShort = (string)dataRow["CreateDateShort"];
if (dataRow["CreateDateShort"] != DBNull.Value)
{
CreateDateShort = Convert.ToDateTime(dataRow["CreateDateShort"]);
}
if (dataRow["UpdatedByID"] != DBNull.Value)
{
UpdatedByID = (int)dataRow["UpdatedByID"];
}
if (dataRow["UpdatedBy"] != DBNull.Value)
{
UpdatedBy = (string)dataRow["UpdatedBy"];
}
if (dataRow["UpdateDate"] != DBNull.Value)
{
UpdateDate = (DateTime)dataRow["UpdateDate"];
}
//if (dataRow["UpdateDateShort"] != DBNull.Value) UpdateDateShort = (string)dataRow["UpdateDateShort"];
if (dataRow["UpdateDateShort"] != DBNull.Value)
{
UpdateDateShort = Convert.ToDateTime(dataRow["UpdateDateShort"]);
}
if (dataRow["JNPWorkflowStatusID"] != DBNull.Value)
{
JNPWorkflowStatusID = (int)dataRow["JNPWorkflowStatusID"];
}
if (dataRow["JNPWorkflowStatus"] != DBNull.Value)
{
JNPWorkflowStatus = (string)dataRow["JNPWorkflowStatus"];
}
if (dataRow["JNPScheduleStatus"] != DBNull.Value)
{
JNPScheduleStatus = (enumScheduleStatus)dataRow["JNPScheduleStatus"];
}
if (dataRow["JNPNextScheduleStatus"] != DBNull.Value)
{
JNPNextScheduleStatus = (string)dataRow["JNPNextScheduleStatus"];
}
if (dataRow["CheckID"] != DBNull.Value)
{
CheckID = (long)dataRow["CheckID"];
}
if (dataRow["CheckedOutByID"] != DBNull.Value)
{
CheckedOutByID = (int)dataRow["CheckedOutByID"];
}
if (dataRow["CheckedOutDate"] != DBNull.Value)
{
CheckedOutDate = (DateTime)dataRow["CheckedOutDate"];
}
if (dataRow["CheckedOutBy"] != DBNull.Value)
{
CheckedOutBy = (string)dataRow["CheckedOutBy"];
}
CanViewJNP = (bool)dataRow["CanViewJNP"];
CanEditJNP = (bool)dataRow["CanEditJNP"];
CanContinueEditJNP = (bool)dataRow["CanContinueEditJNP"];
CanFinishEditJNP = (bool)dataRow["CanFinishEditJNP"];
}
catch (Exception ex)
{
ExceptionBase.HandleException(ex);
}
}
public DevModule(IJatuhTempoBusiness jatuhTempoBusiness, ISettingClientBusiness settingClientBusiness, ISPTPDDetailBusiness sPTPDDetailBusiness, IUserActivityBusiness userActivityBusiness, IUserSettingColumnBusiness userSettingColumnBusiness, IUserTransactionBusiness userTransactionBusiness, IUserTransactionDetailBusiness userTransactionDetailBusiness)
{
_jatuhTempoBusiness = jatuhTempoBusiness;
_settingClientBusiness = settingClientBusiness;
_sPTPDDetailBusiness = sPTPDDetailBusiness;
_userActivityBusiness = userActivityBusiness;
_userSettingColumnBusiness = userSettingColumnBusiness;
_userTransactionBusiness = userTransactionBusiness;
_userTransactionDetailBusiness = userTransactionDetailBusiness;
Get["/testConnection"] = parameter =>
{
var jsonBody = string.Empty;
try
{
List <ExceptionPort> listPort = new List <ExceptionPort>();
listPort = _settingClientBusiness.RetrievePortException();
log.Debug("Get list port");
jsonBody = JsonConvert.SerializeObject(listPort);
log.Debug("Connection Success");
return(Response.AsJson(new { value = "OK", body = jsonBody }));
}
catch (Exception ex)
{
return(Response.AsJson(new { value = "Error : " + ex.Message, body = jsonBody }));
}
};
Get["/dev/getAllUser"] = parameter =>
{
log.Debug("Start:/dev/getAllUser");
List <string> lstUsr = new List <string>();
try
{
lstUsr = _userSettingColumnBusiness.RetrieveAllUser();
log.Debug("Get User Success");
}
catch (Exception ex)
{
//log.Debug( "Get User Failed : " + ex.Message );
log.Fatal("Error:/dev/getAllUser", ex);
return(Response.AsJson(new { code = HttpStatusCode.NotFound, message = "Data Not Found", body = string.Empty }));
}
var jsonBody = JsonConvert.SerializeObject(lstUsr);
return(Response.AsJson(new { code = HttpStatusCode.OK, message = "Ok", body = jsonBody }));
};
Get["/dev/getSerialKeyIsExist"] = parameter =>
{
log.Debug("Start:/dev/getSerialKeyIsExist");
try
{
string body = Nancy.IO.RequestStream.FromStream(Request.Body).AsString();
bool isFound = _userSettingColumnBusiness.IsSerialFound(body);
if (isFound)
{
log.Debug("Serial is found");
return(Response.AsJson(new { code = HttpStatusCode.OK, message = "Found", body = "True" }));
}
else
{
log.Debug("Serial not found");
return(Response.AsJson(new { code = HttpStatusCode.OK, message = "Not", body = "False" }));
}
}
catch (Exception ex)
{
//log.Debug( "Get User Failed : " + ex.Message );
log.Fatal("Error:/dev/getSerialKeyIsExist", ex);
return(Response.AsJson(new { code = HttpStatusCode.NotFound, message = "Data Not Found", body = string.Empty }));
}
};
Get["/dev/GetUrlApi"] = parameter =>
{
log.Debug("Start:/dev/GetUrlApi");
string urlApi = string.Empty;
urlApi = _userActivityBusiness.GetUrlApi();
try
{
var jsonBody = JsonConvert.SerializeObject(urlApi);
return(Response.AsJson(new { code = HttpStatusCode.OK, message = "Ok", body = jsonBody }));
}
catch (Exception ex)
{
return(Response.AsJson(new { code = HttpStatusCode.InternalServerError, message = $"Error, {ex.Message}", body = string.Empty }));
}
};
Get["/dev/GetTarifPajak"] = parameter =>
{
log.Debug("Start:/dev/GetTarifPajak");
List <JenisPajak> lstPajak = new List <JenisPajak>();
try
{
//Get All Tarif Pajak
lstPajak = _userSettingColumnBusiness.RetrieveTarifAll();
log.Debug("Get tarif pajak success");
}
catch (Exception ex)
{
log.Fatal("Error:/dev/GetTarifPajak", ex);
return(Response.AsJson(new { code = HttpStatusCode.NotFound, message = "Data Not Found", body = string.Empty }));
}
var jsonBody = JsonConvert.SerializeObject(lstPajak);
return(Response.AsJson(new { code = HttpStatusCode.OK, message = "OK", body = jsonBody }));
};
Post["/dev/postLastErrorDate"] = parameter =>
{
try
{
log.Info("Start : /dev/postLastErrorDate");
string ip = Request.UserHostAddress;
string body = Nancy.IO.RequestStream.FromStream(Request.Body).AsString();
log.Info("Deserialize object from json body");
var setting = JsonConvert.DeserializeObject <string>(body);
//Get Last Error Date By status_error
LastErrorResponse resp = new LastErrorResponse();
resp.TanggalError = DateTime.Now.Date;
resp.TanggalError = _userActivityBusiness.GetLastErrorDate(setting);
var jsonBody = JsonConvert.SerializeObject(resp);
log.Info("End : /dev/postLastErrorDate");
return(Response.AsJson(new { code = HttpStatusCode.OK, body = jsonBody }));
}
catch (Exception ex)
{
log.Fatal("Error : /dev/postLastErrorDate", ex);
return(Response.AsJson(new { code = HttpStatusCode.InternalServerError, message = $"Error, {ex.Message}" }));
throw;
}
};
Post["/dev/postSettingClient"] = parameter =>
{
try
{
log.Info("Start : /dev/postSettingClient");
log.Info($"incoming request from IP:{this.Request.UserHostAddress}");
string body = Nancy.IO.RequestStream.FromStream(Request.Body).AsString();
log.Info("Deserialize object from json body");
JsonSetting setting = JsonConvert.DeserializeObject <JsonSetting>(body);
#region Inserting Data Setting Client
log.Info("Inserting data setting client");
string username = string.Empty;
foreach (var item in setting.list_user)
{
_settingClientBusiness.InsertUserClient(item.userName, item.idMachine, item.guid, item.phone, item.mail, item.port);
username = item.userName;
}
foreach (var item in setting.list_nop)
{
_settingClientBusiness.InsertUserNop(username, item.nop, item.jenisPajak);
}
foreach (var item in setting.settings)
{
_settingClientBusiness.InsertUserSettingColumn(username, item.nop, item.column_name, item.column_text);
}
#endregion
//insert xmlcontent
_settingClientBusiness.InsertXmlFile(username, "setUpload.xml", setting.xml_content, setting.jenFile, setting.separator);
log.Info("End : /dev/postSettingClient");
return(Response.AsJson(new { code = HttpStatusCode.OK }));
}
catch (System.Exception ex)
{
log.Fatal("Error : /dev/postSettingClient", ex);
return(Response.AsJson(new { code = HttpStatusCode.InternalServerError, message = $"Error, {ex.Message}" }));
throw;
}
};
Post["/dev/postSettingClientWithParam"] = parameter =>
{
try
{
log.Info("Start : /dev/postSettingClientWithParam");
log.Info($"incoming request from IP:{this.Request.UserHostAddress}");
var body = Nancy.IO.RequestStream.FromStream(Request.Body).AsString();
log.Info("Deserialize object from json body");
User user = JsonConvert.DeserializeObject <User>(body);
//Retrieve Data User Setting
if (user != null)
{
log.Info("Checking user exist");
UserClient userClient = _settingClientBusiness.RetrieveUserClient(user.userName, user.idMachine, user.guid).FirstOrDefault();
if (userClient != null)
{
log.Info("Retrieve data setting client from database");
List <UserSettingColumn> listSettingColumn = _userSettingColumnBusiness.RetrieveSettingColumnByUsername(userClient.Username);
log.Info("Serialize data to json");
var jsonBody = JsonConvert.SerializeObject(listSettingColumn);
log.Info("End : /dev/postSettingClientWithParam");
return(Response.AsJson(new { code = HttpStatusCode.OK, message = "Success", body = jsonBody }));
}
else
{
log.Info("End : /dev/postSettingClientWithParam");
return(Response.AsJson(new { code = HttpStatusCode.NotFound, message = "Data Not Found", body = string.Empty }));
}
}
else
{
log.Info("End : /dev/postSettingClientWithParam");
return(Response.AsJson(new { code = HttpStatusCode.Unauthorized, message = "Failed", body = string.Empty }));
}
}
catch (Exception ex)
{
log.Fatal("Error : /dev/postSettingClientWithParam", ex);
return(Response.AsJson(new { code = HttpStatusCode.BadRequest, message = $"Error, {ex.Message}", body = string.Empty }));
}
};
Post["/dev/postTransactionClient"] = parameter =>
{
try
{
log.Info("Start : /dev/postTransactionClient");
string ip = this.Request.UserHostAddress;
log.Info($"incoming request from IP:{ip}");
string body = Nancy.IO.RequestStream.FromStream(Request.Body).AsString();
JsonData jsonData = JsonConvert.DeserializeObject <JsonData>(body);
string username = string.Empty;
string CultureInfos = string.Empty;
username = jsonData.datauser.FirstOrDefault().username;
string bulan = jsonData.masapajak.FirstOrDefault().bulan;
string tahun = jsonData.masapajak.FirstOrDefault().tahun;
CultureInfos = jsonData.items.CultureInfos;
log.Info("generating file json to xml");
DataTable dtTransaksi = new DataTable();
DataTable dtLampiran = new DataTable();
dtTransaksi.CaseSensitive = false;
dtLampiran.CaseSensitive = false;
if (jsonData.items.dtLampiran != null && jsonData.items.dtLampiran.Rows.Count > 0)
{
dtTransaksi = jsonData.items.dtPajak;
}
else
{
log.Info("Data tidak ditemukan ............");
return(Response.AsJson(new { code = HttpStatusCode.NotFound, message = $"Error, {"Data tidak ditemukan."}" }));
}
if (jsonData.items.dtLampiran != null && jsonData.items.dtLampiran.Rows.Count > 0)
{
dtLampiran = jsonData.items.dtLampiran;
}
else
{
dtLampiran = dtTransaksi.Copy();
}
List <UserSettingColumn> lstColumnName = new List <UserSettingColumn>();
lstColumnName = _settingClientBusiness.RetrieveUserSettingColumn(username, dtLampiran.Rows[0]["NOP"].ToString());
string teksTanggal = lstColumnName.Where(m => m.Column_Name.ToUpper().Equals("TGL_TRANSAKSI")).Select(m => m.Column_Text).FirstOrDefault();
if (string.IsNullOrEmpty(teksTanggal))
{
teksTanggal = "TGL_TRANSAKSI";
}
#region Delete Null Rows in First column
DataView dv = dtTransaksi.AsDataView();
dv.RowFilter = teksTanggal + " IS NOT NULL";
dtTransaksi = dv.ToTable();
dv = new DataView();
dv = dtLampiran.AsDataView();
dv.RowFilter = teksTanggal + " IS NOT NULL";
dtLampiran = dv.ToTable();
#endregion
string nop = string.Empty;
bool isFromDatabase = false;
DataColumnCollection col = dtLampiran.Columns;
List <string> lstDistNopLampiran = null;
if (col.Contains("NOP_LAMPIRAN"))
{
lstDistNopLampiran = dtLampiran.AsEnumerable().Select(r => r.Field <string>("NOP_LAMPIRAN")).Distinct().ToList();
isFromDatabase = true;
dtLampiran.Columns["NOP"].ColumnName = "NOP_ALIAS";
dtLampiran.Columns["NOP_LAMPIRAN"].ColumnName = "NOP";
}
else
{
lstDistNopLampiran = dtLampiran.AsEnumerable().Select(r => r.Field <string>("NOP").Trim()).Distinct().ToList();
}
if (lstDistNopLampiran == null || lstDistNopLampiran.Count <= 0)
{
log.Info("List Nop tidak ditemukan ............");
return(Response.AsJson(new { code = HttpStatusCode.NotFound, message = $"Error, List Nop tidak ditemukan." }));
}
List <string> lstNopBlokir = new List <string>();
foreach (var itemNopLampiran in lstDistNopLampiran)
{
nop = itemNopLampiran;
string queryPajak = string.Empty;
//Validate Query
if (isFromDatabase)
{
List <BusinessLogic.queryData> queryLst = _userSettingColumnBusiness.RetrieveQueryPajak(username, nop);
if (queryLst != null && queryLst.Count > 0)
{
bool isQueryValid = true;
string queryPajakFromXml = Regex.Replace(jsonData.items.lstQuery.FirstOrDefault().queryPajak, @"\s+", string.Empty);
string queryPajakFromDB = Regex.Replace(queryLst.FirstOrDefault().queryPajak, @"\s+", string.Empty);
queryPajak = jsonData.items.lstQuery.FirstOrDefault().queryPajak;
string queryLampiranFromXml = Regex.Replace(jsonData.items.lstQuery.FirstOrDefault().queryLampiran, @"\s+", string.Empty);
string queryLampiranFromDB = Regex.Replace(queryLst.FirstOrDefault().queryLampiran, @"\s+", string.Empty);
if (!string.Equals(queryPajakFromDB, queryPajakFromXml, StringComparison.OrdinalIgnoreCase))
{
isQueryValid = false;
}
if (!string.Equals(queryLampiranFromXml, queryLampiranFromDB, StringComparison.OrdinalIgnoreCase))
{
isQueryValid = false;
}
if (!isQueryValid)
{
//insert tanggal_error into user_temp_error
DateTime dtmlastTime = dtTransaksi.AsEnumerable().Select(x => x.Field <DateTime>("TGL_TRANSAKSI")).FirstOrDefault();
_userActivityBusiness.InsertUserActivity(username, ip, DateTime.Now, true, "Query tidak sesuai");
log.Info("Query tidak sesuai, mohon periksa kembali. ............");
return(Response.AsJson(new { code = HttpStatusCode.InternalServerError, message = $"Error, Query tidak sesuai, mohon periksa kembali." }));
}
}
else
{
log.Info("Query pada username dan nop tersebut tidak ditemukan ............");
return(Response.AsJson(new { code = HttpStatusCode.NotFound, message = $"Error, Query pada username dan nop tersebut tidak ditemukan." }));
}
}
List <Transaction> lstTransaksi = new List <Transaction>();
//Get nama kolom tanggal transaksi
List <UserSettingColumn> lstKolomName = new List <UserSettingColumn>();
string namaKolomTanggal = string.Empty;
if (!isFromDatabase)
{
lstKolomName = _userSettingColumnBusiness.RetrieveColumnByUserNop(username, nop);
}
else
{
DataColumnCollection colTrans = dtTransaksi.Columns;
foreach (var itemKolom in colTrans)
{
UserSettingColumn userKolom = new UserSettingColumn();
userKolom.Username = username;
userKolom.Nop = nop;
userKolom.Column_Name = itemKolom.ToString();
userKolom.Column_Text = itemKolom.ToString();
lstKolomName.Add(userKolom);
}
}
namaKolomTanggal = lstKolomName.Where(m => m.Column_Name.ToUpper().Contains("TGL_TRANSAKSI")).Select(m => m.Column_Text.ToUpper()).FirstOrDefault();
List <string> namaKolomNilai = lstKolomName.Where(m => m.Column_Name.ToUpper().Contains("PAJAK")).Select(m => m.Column_Text.ToUpper()).ToList();
DataView dvTrans = dtTransaksi.DefaultView;
dvTrans.RowFilter = "NOP=" + nop;
dvTrans.Sort = namaKolomTanggal;
DataTable dtSortTransaction = dvTrans.ToTable();
DateTime tglOld = new DateTime();
double nilaiOld = 0;
bool isNopBlokir = false;
DataRow last = dtSortTransaction.Rows[dtSortTransaction.Rows.Count - 1];
string oldNop = string.Empty;
int oldMP = 0;
int oldThn = 0;
bool isOnDetail = false;
foreach (DataRow dRow in dtSortTransaction.Rows)
{
DateTime tanggal = dRow[namaKolomTanggal].AsDateTime();
int mp = tanggal.Month;
int thn = tanggal.Year;
//check sptpd is generate
if (string.Compare(oldNop, dRow["NOP"].ToString()) != 0)
{
if (oldMP != mp)
{
if (oldThn != thn)
{
isOnDetail = _sPTPDDetailBusiness.isSptpdDetailByNop(dRow["NOP"].ToString(), mp, thn);
}
}
oldNop = dRow["NOP"].ToString();
oldMP = mp;
oldThn = thn;
}
else
{
oldNop = dRow["NOP"].ToString();
oldMP = mp;
oldThn = thn;
}
if (isOnDetail)
{
lstNopBlokir.Add(dRow["NOP"].ToString());
isNopBlokir = true;
break;
}
double nilaiPajak = 0;
try
{
for (int iNilai = 0; iNilai < namaKolomNilai.Count; iNilai++)
{
if (string.Compare(namaKolomNilai[iNilai].ToString(), "-") != 0)
{
nilaiPajak += dRow[namaKolomNilai[iNilai]].AsDouble();
}
}
}
catch (Exception ex)
{
throw ex;
}
if (tglOld.Year == 1)
{
tglOld = tanggal;
nilaiOld = nilaiPajak;
if (dRow == last)
{
Transaction itemTrans = new Transaction();
itemTrans = new Transaction();
itemTrans.tanggal = tglOld;
itemTrans.total = nilaiOld;
lstTransaksi.Add(itemTrans);
break;
}
}
else
{
if (tglOld.Date == tanggal.Date)
{
if (dRow == last)
{
nilaiOld += nilaiPajak;
Transaction itemTrans = new Transaction();
itemTrans = new Transaction();
itemTrans.tanggal = tglOld;
itemTrans.total = nilaiOld;
lstTransaksi.Add(itemTrans);
break;
}
nilaiOld += nilaiPajak;
}
else
{
Transaction itemTrans = new Transaction();
itemTrans.tanggal = tglOld;
itemTrans.total = nilaiOld;
lstTransaksi.Add(itemTrans);
tglOld = tanggal.Date;
nilaiOld = nilaiPajak;
if (dRow == last)
{
itemTrans = new Transaction();
itemTrans.tanggal = tglOld;
itemTrans.total = nilaiOld;
lstTransaksi.Add(itemTrans);
}
}
}
}
if (isNopBlokir)
{
continue;
}
log.Info("inserting data to user transaction");
//cek transaction isExist
IEnumerable <UserTransaction> lstTransExist = new List <UserTransaction>();
lstTransExist = _userTransactionBusiness.RetrieveUserTransactionByMonth(username, lstTransaksi.FirstOrDefault().tanggal.Month, lstTransaksi.FirstOrDefault().tanggal.Year);
//insert into user_transaction
foreach (var item in lstTransaksi)
{
bool isExist = lstTransExist.Where(m => string.Compare(m.Tanggal.ToString("dd-MM-yyyy"), item.tanggal.ToString("dd-MM-yyyy")) == 0 &&
m.Nop.Equals(nop)).ToList().Count > 0;
try
{
if (isExist)
{
log.Info("update data from user " + username);
_userTransactionBusiness.UpdatePajakUserTransaction(username, nop, item.tanggal, item.total);
}
else
{
log.Info("insert data from user " + username);
_userTransactionBusiness.InsertUserTransaction(username, item.tanggal, item.total, ip, string.Empty, false, nop);
}
}
catch (Exception ex)
{
log.Info("insert or update error : " + ex.Message);
}
}
log.Info("inserting data to user transaction detail");
//insert into user_transaction_detail
string dir = System.IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "transaction.xml");
//Replace nama kolom untuk no_invoice dan tgl_transaksi
if (!isFromDatabase)
{
List <UserSettingColumn> lstSetCol = _settingClientBusiness.RetrieveUserSettingColumn(username, nop);
string noInvoice = lstSetCol.Where(x => x.Column_Name.ToUpper().Equals("NO_INVOICE")).Select(x => x.Column_Text.ToUpper()).FirstOrDefault();
string tglTransaksi = lstSetCol.Where(x => x.Column_Name.ToUpper().Equals("TGL_TRANSAKSI")).Select(x => x.Column_Text.ToUpper()).FirstOrDefault();
dtLampiran.Columns[noInvoice].ColumnName = "NO_INVOICE";
dtLampiran.Columns[tglTransaksi].ColumnName = "TGL_TRANSAKSI";
}
string colTglName = "TGL_TRANSAKSI";
//Pecah dan simpan sesuai tanggal transaksi
#region Old Code
//string[] arrSelect = queryPajak.ToUpper().Split(new string[] { "FROM" }, StringSplitOptions.None);
//string[] arrSplit = arrSelect[0].Split(',');
//string dbColDateName = string.Empty;
//if (isFromDatabase)
//{
// foreach (string item in arrSplit)
// {
// if (item.Contains("TGL_TRANSAKSI"))
// {
// dbColDateName = item;
// break;
// }
// }
//}
//else
//{
// dbColDateName = namaKolomTanggal;
//}
//string[] arrTglName = dbColDateName.Replace("SELECT ", string.Empty).Split(' ');
//string colTglName = string.Empty;
//if (arrTglName.Count() > 1)
// colTglName = arrTglName[arrTglName.Count() - 2];
//else
// colTglName = arrTglName[0];
//if (string.IsNullOrEmpty(colTglName))
//{
// if (string.IsNullOrEmpty(queryPajak))
// colTglName = "TGL_TRANSAKSI";
//}
#endregion
//DataView dvTrans = new DataView(dtLampiran);
//dvTrans.RowFilter = $"NOP={nop}";
//DataTable dtDistDate = dvTrans.ToTable(true, colTglName);
DataTable dtTransByNop = dtLampiran.AsEnumerable().Where(m => m.Field <string>("NOP").Equals(nop)).CopyToDataTable();
DataTable dtDistDate = new DataTable();
dtDistDate.Columns.Add(colTglName, typeof(DateTime));
if (dtTransByNop.Columns[colTglName].DataType != typeof(DateTime))
{
DataTable copyTransTable = dtTransByNop.Clone();
copyTransTable.Columns[colTglName].DataType = typeof(DateTime);
foreach (DataRow item in dtTransByNop.Rows)
{
DataRow nRowTransTable = copyTransTable.NewRow();
nRowTransTable.ItemArray = item.ItemArray;
copyTransTable.Rows.Add(nRowTransTable);
}
dtTransByNop = copyTransTable;
}
DateTime oldDate = DateTime.MinValue;
foreach (DataRow item in dtTransByNop.Rows)
{
//if (Convert.ToInt32(item["NO"]) == 1168)
//{
//}
DateTime newDate = Convert.ToDateTime(item["TGL_TRANSAKSI"]).Date;
if (string.Compare(oldDate.ToString("dd-MM-yyyy"), newDate.ToString("dd-MM-yyyy")) != 0)
{
DataRow newRow = dtDistDate.NewRow();
newRow[colTglName] = newDate;
dtDistDate.Rows.Add(newRow);
oldDate = newDate;
}
}
foreach (DataRow dRow in dtDistDate.Rows)
{
DataSet ds = new DataSet();
DateTime tglTrans = DateTime.MinValue;
try
{
tglTrans = Convert.ToDateTime(dRow[colTglName], new CultureInfo(CultureInfos));
}
catch (Exception ex)
{
log.Info("Date format error......" + ex.Message);
}
DataView vw = dtTransByNop.AsDataView();
string startDate = tglTrans.ToString("MM/dd/yyyy 00:00:00", new CultureInfo("en-US"));
string endDate = tglTrans.ToString("MM/dd/yyyy 23:59:59", new CultureInfo("en-US"));
string filter = string.Format(colTglName + " >= #{0}# AND " + colTglName + "<=#{1}#",
startDate, endDate);
vw.RowFilter = filter;
ds.Tables.Add(vw.ToTable());
ds.WriteXml(dir);
string xmlString = System.IO.File.ReadAllText(dir);
string nopDetail = string.Empty;
//Cek username, nop, bulan and tahun is exist
bool isDetailExist = false;
try
{
//todo delete 1 row below List<UserTransactionDetail> lstDetail = UserTransactionDetailBusiness.RetrieveUserDetailTransactionByDateTransaction(nop, tglTrans).ToList();
List <UserTransactionDetail> lstDetail = null;
if (lstDetail != null && lstDetail.Count > 0)
{
isDetailExist = true;
}
//Delete if username, nop, bulan and tahun is exist
if (isDetailExist)
{
_userTransactionDetailBusiness.DeleteUserDetailTransaction(nop, tglTrans);
}
// insert Trasaction Detail
_userTransactionDetailBusiness.InsertUserTransactionDetail(username, dir, bulan.AsInt32(), tahun.AsInt32(), tglTrans, ip, xmlString, nop);
}
catch (Exception ex)
{
return(Response.AsJson(new { code = HttpStatusCode.MethodNotAllowed, message = "Insert trasanction detail gagal, " + ex.Message }));
}
}
}
log.Info("End : /dev/postTransactionClient");
if (lstNopBlokir != null && lstNopBlokir.Count > 0)
{
string nopBlokir = string.Empty;
foreach (var itemBlokir in lstNopBlokir)
{
nopBlokir += itemBlokir + ",";
}
nopBlokir = nopBlokir.Remove(nopBlokir.Length - 1);
return(Response.AsJson(new { code = HttpStatusCode.MethodNotAllowed, message = "nop (" + nopBlokir + ") pada masapajak tersebut, sudah tergenerate." }));
}
else
{
try
{
var connection = SignalR.Utilities.ConnectionMap.GetConnectionMap(username);
if (connection.Value.IsRequesting)
{
connection.Value.IsRequesting = false;
}
}
catch
{
}
return(Response.AsJson(new { code = HttpStatusCode.OK, message = "Transaksi berhasil tersimpan" }));
}
}
catch (System.Exception ex)
{
log.Fatal("Error: /dev/postTransactionClient", ex);
return(Response.AsJson(new { code = HttpStatusCode.InternalServerError, message = $"Error, {ex.Message}" }));
//throw;
}
};
Post["/dev/postUserActivity"] = parameter =>
{
try
{
log.Info("Start : /dev/postUserActivity");
string ip = this.Request.UserHostAddress;
log.Info($"incoming request from IP:{ip}");
string body = Nancy.IO.RequestStream.FromStream(Request.Body).AsString();
log.Info("Deserializing Object ............");
UserActivity userActivity = JsonConvert.DeserializeObject <UserActivity>(body);
DateTime dateNow = DateTime.Now;
DateTime tglClient = DateTime.MinValue;
try
{
tglClient = Convert.ToDateTime(userActivity.ActivityDate, new CultureInfo(userActivity.CultureInfos));
}
catch (Exception ex)
{
log.Info("Date format error......" + ex.Message);
}
tglClient = Convert.ToDateTime(tglClient, new CultureInfo("en-US"));
if (userActivity != null)
{
log.Info("Inserting user activity ............");
bool status = false;
if (string.Compare(userActivity.StatusError, "1") == 0)
{
status = true;
}
try
{
string ipClient = userActivity.IPClient;
if (string.IsNullOrEmpty(userActivity.IPClient))
{
ipClient = ip;
}
_userActivityBusiness.InsertUserActivity(userActivity.Username, ipClient, dateNow, status, userActivity.Keterangan);
var jsonBody = JsonConvert.SerializeObject(dateNow, Formatting.None, new IsoDateTimeConverter()
{
DateTimeFormat = "yyyy-MM-dd hh:mm:ss"
});
log.Info("End : /dev/postUserActivity");
return(Response.AsJson(new { code = HttpStatusCode.OK, message = "User Activity Berhasil tersimpan.", body = jsonBody }));
}
catch (Exception ex)
{
log.Fatal("Error : /dev/postUserActivity", ex);
return(Response.AsJson(new { code = HttpStatusCode.InternalServerError, message = $"Error, {ex.Message}" }));
}
}
else
{
log.Info("End : /dev/postUserActivity");
return(Response.AsJson(new { code = HttpStatusCode.Unauthorized, message = "Failed while deserializing object, data not found", body = string.Empty }));
}
}
catch (Exception ex)
{
log.Fatal("Error : /dev/postUserActivity", ex);
return(Response.AsJson(new { code = HttpStatusCode.InternalServerError, message = $"Error, {ex.Message}" }));
}
};
Post["/dev/RetrieveCpuId"] = Parameter =>
{
try
{
log.Info("Start : /dev/RetrieveCpuId");
string username = string.Empty;
string cpuId = string.Empty;
string body = Nancy.IO.RequestStream.FromStream(Request.Body).AsString();
log.Info("Deserializing Object ............");
string[] arrStr = body.Split(',');
if (arrStr.Count() > 0)
{
for (int iArr = 0; iArr < arrStr.Count(); iArr++)
{
username += "'" + arrStr[iArr] + "',";
}
username = username.Remove(username.Length - 1);
}
List <UserClient> lstUsr = _settingClientBusiness.RetrieveUserClient(username);
if (lstUsr != null && lstUsr.Count > 0)
{
cpuId = lstUsr.FirstOrDefault().Id_Machine;
}
log.Info("End : /dev/RetrieveCpuId");
return(Response.AsJson(new { code = HttpStatusCode.OK, message = "OK", cpuId = cpuId }));
}
catch (Exception ex)
{
log.Info("End : /dev/RetrieveCpuId");
return(Response.AsJson(new { code = HttpStatusCode.FailedDependency, message = ex.Message, cpuId = "" }));
}
};
Post["/dev/RetrieveJatuhTempo"] = parameter =>
{
try
{
log.Info("Start : /dev/RetrieveJatuhTempo");
string ip = this.Request.UserHostAddress;
log.Info($"incoming request from IP:{ip}");
string body = Nancy.IO.RequestStream.FromStream(Request.Body).AsString();
log.Info("Deserializing Object ............");
RequestDueDate dueDate = JsonConvert.DeserializeObject <RequestDueDate>(body);
string tanggal = string.Empty;
if (dueDate != null)
{
JatuhTempo jthTempo = _jatuhTempoBusiness.RetrieveJatuhTempo(dueDate.masapajak.AsInt32(), dueDate.tahunpajak.AsInt32());
if (jthTempo != null)
{
tanggal = jthTempo.Tgl_Jatuh_Tempo.ToString("dd/MM/yyyy");
log.Info("End : /dev/RetrieveJatuhTempo");
return(Response.AsJson(new { code = HttpStatusCode.OK, tanggal = tanggal }));
}
else
{
log.Info("End : /dev/RetrieveJatuhTempo");
return(Response.AsJson(new { code = HttpStatusCode.ExpectationFailed }));
}
}
else
{
log.Info("End : /dev/RetrieveJatuhTempo");
return(Response.AsJson(new { code = HttpStatusCode.Unauthorized, message = "Failed while deserializing object, data not found", body = string.Empty }));
}
}
catch (Exception ex)
{
log.Fatal("Error : /dev/RetrieveJatuhTempo", ex);
return(Response.AsJson(new { code = HttpStatusCode.InternalServerError, message = $"Error, {ex.Message}" }));
}
};
}
private void grdData_GridRowDataBound(object sender, GridViewRowEventArgs e)
{
GridViewRow row = e.Row;
FormatRowDisplay(ref row);
if (DCC.Contains("X") && DCC.Contains("CR_ID"))
{
if (this.CanEditAOR)
{
row.Cells[DCC.IndexOf("X")].Style["text-align"] = "center";
row.Cells[DCC.IndexOf("X")].Controls.Add(CreateCheckBox(row.Cells[DCC.IndexOf("CR_ID")].Text));
}
}
if (DCC.Contains("X") && DCC.Contains("Work Task"))
{
if (this.CanEditAOR)
{
row.Cells[DCC.IndexOf("X")].Style["text-align"] = "center";
row.Cells[DCC.IndexOf("X")].Controls.Add(CreateCheckBox(row.Cells[DCC.IndexOf("Work Task")].Text));
}
if (this.CanViewWorkItem)
{
row.Cells[DCC.IndexOf("Work Task")].Style["text-align"] = "center";
row.Cells[DCC.IndexOf("Work Task")].Controls.Add(CreateLink(row.Cells[DCC.IndexOf("Work Task")].Text));
}
}
}
private static String BuildEmails(DataTable Companies, bool PerformBuild = true, bool PerformIncrementalBuild = false, bool LogUnmatchedEmails = false, bool DisplayDetailedOutput = false, String EstimatedBy = "0", String TargetContactID = "")
{
if (HttpContext.Current != null)
{
Context = HttpContext.Current;
}
else if (Context == null)
{
throw new Exception("No HttpContext set for the EmailBuilder, please pass HttpContext.Current, i.e. EmailBuilder.Context = HttpContext.Current;");
}
DataColumnCollection columns = Companies.Columns;
if (!columns.Contains("CompanyID") || !columns.Contains("CompanyName"))
{
throw new Exception("Error, the Companies DataTable for function 'BuildEmailsByCompanyList' must contain at least a column named 'CompanyID' and a column named 'CompanyName'.");
}
Stopwatch sw = new Stopwatch();
sw.Start();
String BuildOutput = String.Empty;
int BuiltEmails = 0;
int TotalContactsWithEmail = 0;
double TotalContactsWithoutEmail = 0;
int CompaniesCrawled = Companies.Rows.Count;
String[] DelimeterVariants = new String[] { ".", "", "-", "_" };
Dictionary <Int32, Int32> OverallEmailPatternMatchWeights = new Dictionary <Int32, Int32>();
// For each company, get list of contacts, in order of email (nulls are replaced such that they always appear last)
for (int cpy = 0; cpy < Companies.Rows.Count; cpy++)
{
String CompanyID = Companies.Rows[cpy]["CompanyID"].ToString();
String CompanyName = Context.Server.HtmlEncode(Companies.Rows[cpy]["CompanyName"].ToString());
bool AnyMatchedForThisCompany = false;
bool AnyBuiltForThisCompany = false;
if (DisplayDetailedOutput)
{
BuildOutput += "<br/><br/><b>--------------------- START OF COMPANY '" + CompanyName
+ "' BUILD ----------------------</b><br/>Attempting to build e-mails for contacts at company <b>'" + CompanyName + "'</b>..<br/>";
}
// Get all contacts at this company with an email
String qry = "SELECT ContactID, " +
"LOWER(TRIM(REPLACE(REPLACE(REPLACE(REPLACE(FirstName,'.',''),' ',''),'''',''),',',''))) as 'fn_clean', " + // ,'-','') // keep hyphenated names, and hope the e-mail address honors them
"LOWER(TRIM(REPLACE(REPLACE(REPLACE(REPLACE(LastName,'.',''),' ',''),'''',''),',',''))) as 'ln_clean', " + // ,'-','')
"TRIM(LOWER(Email)) as 'email' " +
"FROM db_contact WHERE " + EmailRequirementsSQL + NameRequirementsSQL + " AND db_contact.CompanyID=@CompanyID " +
"ORDER BY email";
DataTable dt_contacts_with_email = SQL.SelectDataTable(qry, "@CompanyID", CompanyID);
TotalContactsWithEmail += dt_contacts_with_email.Rows.Count;
// Determine weighting for each pattern when compared against each contact with an email at this company
Dictionary <Int32, Int32> CompanyEmailPatternMatchWeights = new Dictionary <Int32, Int32>();
Dictionary <String, Int32> CompanyEmailDelimeterMatchWeights = new Dictionary <String, Int32>();
Dictionary <String, Int32> CompanyEmailDomainMatchWeights = new Dictionary <String, Int32>();
for (int ctc = 0; ctc < dt_contacts_with_email.Rows.Count; ctc++)
{
ContactEmailDetails Contact = new ContactEmailDetails(
dt_contacts_with_email.Rows[ctc]["fn_clean"].ToString(),
dt_contacts_with_email.Rows[ctc]["ln_clean"].ToString(),
dt_contacts_with_email.Rows[ctc]["email"].ToString());
String[] EmailPatterns = BuildEmailPatterns(Contact);
if (DisplayDetailedOutput)
{
BuildOutput += "<br/> <b>Contact with E-mail #" + (ctc + 1) + " [Clean Lower] "
+ (Context.Server.HtmlEncode(Contact.FirstNameClean) + " " + Context.Server.HtmlEncode(Contact.LastNameClean)).Trim() + "  " + Context.Server.HtmlEncode(Contact.Email) + "</b><br/>";
}
bool PatternMatch = false;
for (int P = 0; P < EmailPatterns.Length; P++)
{
// Ignore email patterns which contain invalid initials
if (!EmailPatterns[P].Contains("¬") || DisplayDetailedOutput)
{
String EmailPattern = EmailPatterns[P] + Contact.EmailDomain;
if (EmailPatterns[P].Contains("#"))
{
for (int D = 0; D < DelimeterVariants.Length; D++)
{
if (EmailPattern.Replace("#", DelimeterVariants[D]) == Contact.Email)
{
PatternMatch = true;
// add delimeter to weights
if (CompanyEmailDelimeterMatchWeights.ContainsKey(DelimeterVariants[D]))
{
CompanyEmailDelimeterMatchWeights[DelimeterVariants[D]] += 1;
}
else
{
CompanyEmailDelimeterMatchWeights.Add(DelimeterVariants[D], 1);
}
EmailPattern = EmailPattern.Replace("#", DelimeterVariants[D]);
break;
}
}
}
else
{
PatternMatch = (EmailPattern == Contact.Email);
}
if (DisplayDetailedOutput)
{
String BuildableColor = "<font color='red'>";
if (PatternMatch)
{
BuildableColor = "<font color='green'>";
}
if (EmailPattern.Contains("¬"))
{
EmailPattern = "<b> " + Context.Server.HtmlEncode(EmailPattern.Replace("¬", String.Empty)) + "</b>";
}
else
{
EmailPattern = Context.Server.HtmlEncode(EmailPattern);
}
BuildOutput += " " + BuildableColor + "<i>[Pattern " + P + "]  " + EmailPattern + "</i></font><br/>";
}
if (PatternMatch)
{
AnyMatchedForThisCompany = true;
// add email pattern to company pattern weights
if (CompanyEmailPatternMatchWeights.ContainsKey(P))
{
CompanyEmailPatternMatchWeights[P] += 1;
}
else
{
CompanyEmailPatternMatchWeights.Add(P, 1);
}
// add email pattern to overall pattern weights
if (OverallEmailPatternMatchWeights.ContainsKey(P))
{
OverallEmailPatternMatchWeights[P] += 1;
}
else
{
OverallEmailPatternMatchWeights.Add(P, 1);
}
// add email domain to company domain weights
if (CompanyEmailDomainMatchWeights.ContainsKey(Contact.EmailDomain))
{
CompanyEmailDomainMatchWeights[Contact.EmailDomain] += 1;
}
else
{
CompanyEmailDomainMatchWeights.Add(Contact.EmailDomain, 1);
}
break;
}
}
}
if (LogUnmatchedEmails && !PatternMatch)
{
Util.Debug(Contact.FirstNameClean + " " + Contact.LastNameClean + " - " + Contact.Email + " (At company " + cpy + " of " + Companies.Rows.Count + ")");
}
}
// Get all contacts at this company without an e-mail
qry = qry.Replace(EmailRequirementsSQL, "email IS NULL ");
DataTable dt_contacts_without_email = SQL.SelectDataTable(qry, "@CompanyID", CompanyID);
TotalContactsWithoutEmail += dt_contacts_without_email.Rows.Count;
// If any have matched, build emails
String EmailsBuiltStatsSummary = String.Empty;
if (AnyMatchedForThisCompany)
{
// Output weights for this company
if (DisplayDetailedOutput)
{
BuildOutput += "<br/>E-mail pattern match weight stats for company <b>'" + CompanyName + "':</b><br/>";
foreach (var item in CompanyEmailPatternMatchWeights.OrderByDescending(r => r.Value).Take(100))
{
BuildOutput += " Pattern Number " + Context.Server.HtmlEncode(item.Key.ToString())
+ ", matched " + Context.Server.HtmlEncode(Util.CommaSeparateNumber(Convert.ToDouble(item.Value.ToString()), false)) + " times<br/>";
}
}
// Determine highest ranked pattern, domain and delimeter
IOrderedEnumerable <KeyValuePair <Int32, Int32> > CompanyEmailPatternMatchWeightsList = CompanyEmailPatternMatchWeights.OrderByDescending(x => x.Value).ThenBy(x => x.Key);
int HighestRankedPatternID = Convert.ToInt32(CompanyEmailPatternMatchWeightsList.ElementAt(0).Key);
String HighestRankedPatternDelimeter = ".";
foreach (var item in CompanyEmailDelimeterMatchWeights.OrderByDescending(r => r.Value).Take(1))
{
HighestRankedPatternDelimeter = item.Key.ToString();
}
String HighestRankedDomain = CompanyEmailDomainMatchWeights.OrderByDescending(r => r.Value).Take(1).ToString();
foreach (var item in CompanyEmailDomainMatchWeights.OrderByDescending(r => r.Value).Take(1))
{
HighestRankedDomain = item.Key.ToString();
}
// Now apply the highest rated pattern to all contacts at this company who do not have an e-mail
String uqry = "UPDATE db_contact SET email=@ee WHERE ContactID=@ctc_id";
String iqry = "INSERT IGNORE INTO db_contact_email_history (ContactID, Email, Estimated, DataGeekEstimate, EstimatedByUserID, EstimationDate, EmailEstimationPatternID, UsingCleanFirstName, UsingCleanLastName) " +
"VALUES (@ContactID, @Email, 1, 1, @EstimatedBy, CURRENT_TIMESTAMP, @EmailEstimationPatternID, @UsingCleanFirstName, @UsingCleanLastName);";
for (int ctc = 0; ctc < dt_contacts_without_email.Rows.Count; ctc++)
{
String ContactID = dt_contacts_without_email.Rows[ctc]["ContactID"].ToString();
String FirstNameClean = Util.RemoveAccents(dt_contacts_without_email.Rows[ctc]["fn_clean"].ToString());
String LastNameClean = Util.RemoveAccents(dt_contacts_without_email.Rows[ctc]["ln_clean"].ToString());
String EstimatedEmail = ImplementPattern(HighestRankedPatternID, HighestRankedPatternDelimeter, HighestRankedDomain, FirstNameClean, LastNameClean);
if (EstimatedEmail != null)
{
AnyBuiltForThisCompany = true;
int ThisHighestRankedPatternID = HighestRankedPatternID;
bool HasBuiltNewUniqueEmail = false;
if (PerformBuild)
{
// check whether the highest ranked pattern has been used to build an email for this contact before (or an entry has been manually deleted by user)
bool HighestRankedPatternIDAlreadyImplemented = false;
bool HighestRankedPatternIDAlreadyImplementedAndFailed = false;
String ib_qry = "SELECT CASE WHEN EmailEstimateByPatternFailed IS NULL OR EmailEstimateByPatternFailed = 0 THEN 0 ELSE 1 END as 'Failed', Deleted FROM db_contact_email_history WHERE " +
"ContactID=@ContactID AND DataGeekEstimate=1 AND EmailEstimationPatternID IS NOT NULL AND Email=@ThisEstimatedEmail";
DataTable dt_main_pattern_match = SQL.SelectDataTable(ib_qry, new String[] { "@ContactID", "@ThisEstimatedEmail" }, new Object[] { ContactID, EstimatedEmail });
HighestRankedPatternIDAlreadyImplemented = dt_main_pattern_match.Rows.Count > 0;
if (HighestRankedPatternIDAlreadyImplemented)
{
HighestRankedPatternIDAlreadyImplementedAndFailed = dt_main_pattern_match.Rows[0]["Failed"].ToString() == "1" || dt_main_pattern_match.Rows[0]["Deleted"].ToString() == "1";
}
bool FoundAlternativePatternToImplement = false;
if (PerformIncrementalBuild && HighestRankedPatternIDAlreadyImplementedAndFailed && CompanyEmailPatternMatchWeightsList.Count() > 1)
{
// check to make sure we don't have any other patterns which are not marked as failed yet (or entry has been deleted by user)
String ch_qry = "SELECT ContactID FROM db_contact_email_history WHERE ContactID=@ContactID AND DataGeekEstimate=1 AND EmailEstimationPatternID IS NOT NULL AND (EmailEstimateByPatternFailed IS NULL OR EmailEstimateByPatternFailed=0) AND Deleted=0";
bool CanIncrementalBuild = SQL.SelectDataTable(ch_qry, new String[] { "@ContactID", "@ThisEstimatedEmail" }, new Object[] { ContactID, EstimatedEmail }).Rows.Count == 0;
if (CanIncrementalBuild)
{
// start with the second-highest ranked pattern for this company's contacts, build and test
for (int i = 1; i < CompanyEmailPatternMatchWeightsList.Count(); i++)
{
EstimatedEmail = ImplementPattern(CompanyEmailPatternMatchWeightsList.ElementAt(i).Key, HighestRankedPatternDelimeter, HighestRankedDomain, FirstNameClean, LastNameClean);
FoundAlternativePatternToImplement = SQL.SelectDataTable(ib_qry, new String[] { "@ContactID", "@ThisEstimatedEmail" }, new Object[] { ContactID, EstimatedEmail }).Rows.Count == 0;
if (FoundAlternativePatternToImplement)
{
ThisHighestRankedPatternID = CompanyEmailPatternMatchWeightsList.ElementAt(i).Key;
break;
}
}
}
}
HasBuiltNewUniqueEmail = (!HighestRankedPatternIDAlreadyImplemented || FoundAlternativePatternToImplement) && EstimatedEmail.Length <= 100 && (TargetContactID == String.Empty || ContactID == TargetContactID);
// Add email based on selected pattern to email history
if (HasBuiltNewUniqueEmail)
{
if (FirstNameClean.Length > 50)
{
FirstNameClean = FirstNameClean.Substring(0, 49);
}
if (LastNameClean.Length > 50)
{
LastNameClean = LastNameClean.Substring(0, 49);
}
SQL.Insert(iqry,
new String[] { "@ContactID", "@Email", "@EstimatedBy", "@EmailEstimationPatternID", "@UsingCleanFirstName", "@UsingCleanLastName" },
new Object[] { ContactID, EstimatedEmail, EstimatedBy, ThisHighestRankedPatternID, FirstNameClean, LastNameClean });
//// temp, for updating contact table directly
//uqry = "UPDATE db_contact SET Email=@ee, EmailBuiltDate=CURRENT_TIMESTAMP WHERE ContactID=@ContactID";
//SQL.Update(uqry, new String[] { "@ee", "@ContactID" }, new Object[] { EstimatedEmail, ContactID });
}
}
if (DisplayDetailedOutput)
{
if (HasBuiltNewUniqueEmail)
{
EmailsBuiltStatsSummary += "<br/> E-mail built for contact '" + Context.Server.HtmlEncode((FirstNameClean + " " + LastNameClean).Trim()) + "' as <b>" + Context.Server.HtmlEncode(EstimatedEmail) + "</b>" +
" using pattern " + ThisHighestRankedPatternID;
}
else
{
EmailsBuiltStatsSummary += "<br/> E-mail could not be built for contact <b>'" + Context.Server.HtmlEncode((FirstNameClean + " " + LastNameClean).Trim()) + "'</b> as all matched patterns have already been implemented.";
}
}
if (HasBuiltNewUniqueEmail)
{
BuiltEmails++;
}
}
}
// Set highest rated pattern for company, and assign ranking
uqry = "UPDATE db_company SET EmailEstimationPatternID=@EmailEstimationPatternID WHERE CompanyID=@CompanyID";
SQL.Update(uqry, new String[] { "@CompanyID", "@EmailEstimationPatternID" }, new Object[] { CompanyID, HighestRankedPatternID });
}
if (DisplayDetailedOutput)
{
if (!AnyBuiltForThisCompany)
{
BuildOutput += "<br/>No e-mails could be built for this company..<br/>";
}
else
{
BuildOutput += "<br/>E-mails built for company <b>'" + CompanyName + "'</b>:<font color='green'>" + EmailsBuiltStatsSummary + "</font><br/>";
}
BuildOutput += "<br/><b>--------------------- END OF COMPANY '" + CompanyName + "' BUILD ----------------------</b>";
}
}
// Show results of build
String OverallEmailPatternWeights = String.Empty;
foreach (var item in OverallEmailPatternMatchWeights.OrderByDescending(r => r.Value).Take(100))
{
OverallEmailPatternWeights += " Pattern Number " + Context.Server.HtmlEncode(item.Key.ToString())
+ ", matched " + Context.Server.HtmlEncode(Util.CommaSeparateNumber(Convert.ToDouble(item.Value.ToString()), false)) + " times<br/>";
}
if (OverallEmailPatternWeights != String.Empty)
{
OverallEmailPatternWeights = "E-mail Pattern Match Stats:<br/>" + OverallEmailPatternWeights;
}
Double PercentBuilt = 0;
if (BuiltEmails != 0 || TotalContactsWithoutEmail != 0)
{
PercentBuilt = (BuiltEmails / TotalContactsWithoutEmail) * 100;
}
String Results =
"<b><font size='3'>Building E-mails finished...</b></font><p/><b>Overall Build Summary:</b><br/>" +
"Contacts Crawled with E-mail: " + TotalContactsWithEmail + "<br/>" +
"Contacts Crawled without E-mail: " + TotalContactsWithoutEmail + "<br/>" +
"Companies Crawled: " + CompaniesCrawled + "<br/>" +
"E-mails Built: " + BuiltEmails + " (" + PercentBuilt.ToString("N2") + "% of Contacts without E-mail)<br/>" +
"Time Taken: " + sw.Elapsed.ToString("hh\\:mm\\:ss") + "<br/>" +
OverallEmailPatternWeights + "<p>";
if (DisplayDetailedOutput)
{
Results += "<b>Detailed Output:</b>" + BuildOutput;
}
sw.Stop();
return(Results);
}
private void treeView1_NodeMouseClick(object sender, TreeNodeMouseClickEventArgs e)
{
var table = new DataTable();
foreach (var r in dt.Sinhvien_selectMasv(e.Node.Name))
{
lblmasv.Text = e.Node.Name;
lblten.Text = r.HoTen;
lblngay.Text = r.NgaySinh.ToString();
lblgioitinh.Text = r.GioiTinh.ToString();
if (r.GioiTinh.ToString() == "1")
{
lblgioitinh.Text = "Nữ";
}
else
{
lblgioitinh.Text = "Nam";
}
lbldantoc.Text = r.DanToc;
lblnoi.Text = r.NoiSinh;
lbldiachi.Text = r.DiaChi;
lblemail.Text = r.Email;
lblsdt.Text = r.Sdt;
dataGridViewX1.DataSource = table;
}
//var table = new DataTable();
DataColumnCollection col = table.Columns;
if (!col.Contains("MaHP"))
{
table.Columns.Add("MaHP", typeof(String));
}
if (!col.Contains("TenHP"))
{
table.Columns.Add("TenHP", typeof(String));
}
// if (!col.Contains("SoTinChi"))
// table.Columns.Add("SoTinChi", typeof(String));
if (!col.Contains("DiemHP"))
{
table.Columns.Add("DiemHP", typeof(String));
}
if (!col.Contains("DiemChu"))
{
table.Columns.Add("DiemChu", typeof(String));
}
if (!col.Contains("DiemSo"))
{
table.Columns.Add("DiemSo", typeof(String));
}
ketnoi();
cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = "select * from Ketqua where MaSV='" + e.Node.Name + "'";
dr = cmd.ExecuteReader();
dt1 = new DataTable();
dt1.Load(dr);
dataGridViewX1.DataSource = dt1;
conn.Close();
cmd.Dispose();
//foreach(var m in dt.ketqua_selectAll())
//{
// DataRow r = table.NewRow();
// r["MaHP"] = m.MaHP;
// r["TenHP"] = m.TenHP;
// dataGridViewX1.DataSource = table;
//}
}
public SessionObjects AddProfilePic(IConnectToDB _Connect, SessionObjects SO, FormCollection _formCollection)
{
try
{
if (_formCollection.Keys.Count > 1)
{
SecurityHelper securityHelper = new SecurityHelper();
IdentityHelper identityHelper = new IdentityHelper();
ER_DML er_dml = new ER_DML();
add addHelp = new add();
long? identitiesId = ER_Tools.ConvertToInt64(identityHelper.GetIdentityID(_Connect, _formCollection["identitiesUUID"].ToString()));
string fileName = _formCollection["fileName"].ToString();
string fileType = _formCollection["fileType"].ToString();
long? fileSize = ER_Tools.ConvertToInt64(_formCollection["fileSize"].ToString());
byte[] fileContent = Encoding.UTF8.GetBytes(_formCollection["profileImage"].ToString());
Guid? ProfilesUUID = ER_Tools.ConvertToGuid(_formCollection["profilesUUID"].ToString());
long? Profiles_ID = GetProfileId(_Connect, ProfilesUUID.ToString());
long? profileImagesId = 0;
var profileImagesDT = GetImageIdByProfile(_Connect, Profiles_ID);
DataColumnCollection _dccColumnID = profileImagesDT.Columns;
if (_dccColumnID.Contains("PROFILE_IMAGES_ID") && profileImagesDT.Rows.Count > 0)
{
Values.UpdateProfileImages ProfileImagesModel = null;
ProfileImagesModel = addHelp.UPDATE_ENTRY_Profile_Images(_Connect, new Values.UpdateProfileImages
{
I_PROFILE_IMAGES_ID = profileImagesDT.Rows[0].Field <long?>("PROFILE_IMAGES_ID"),
I_PROFILES_ID = Profiles_ID,
I_FILE_NAME = fileName,
I_FILE_SIZE = fileSize,
I_CONTENT_TYPE = fileType,
I_VALUE = fileContent
});
profileImagesId = ProfileImagesModel.O_PROFILE_IMAGES_ID;
//Enable Profile Image
er_dml.TOGGLE_OBJECT(_Connect, "PROFILE_IMAGES", profileImagesId, "Y");
}
else
{
//Values.AddProfilesSecPriv ProfilesSecPrivModel = null;
//ProfilesSecPrivModel = addHelp.ADD_ENTRY_Profiles_Sec_Priv(_Connect, new Values.AddProfilesSecPriv
//{
// I_OBJECT_TYPE = "Permission",
// I_PROFILES_ID = Profiles_ID,
// I_PRIVILEGES_ID = ER_Tools.ConvertToInt64(securityHelper.GetPrivID(_Connect, "CREATE OBJECT")),
// I_ENABLED = 'Y',
// I_IDENTITIES_ID = identitiesId
//});
Values.AddProfileImages ProfileImagesModel = null;
ProfileImagesModel = addHelp.ADD_ENTRY_Profile_Images(_Connect, new Values.AddProfileImages
{
I_IDENTITIES_ID = identitiesId,
I_PROFILES_UUID = ProfilesUUID,
I_ENABLED = 'Y',
I_FILE_NAME = fileName,
I_FILE_SIZE = fileSize,
I_CONTENT_TYPE = fileType,
I_VALUE = fileContent
});
}
//Set Profile Image
_session.SetString("ProfileImage", GetProfileImage(_Connect, identitiesId));
}
}
catch
{
}
return(SO);
}
public bool RecoverPassword(IConnectToDB _Connect, string _emailaddress)
{
add addHelp = new add();
_DynamicOutputProcedures DynamicOutput = new _DynamicOutputProcedures();
List <DynamicModels.RootReportFilter> Filters = new List <DynamicModels.RootReportFilter>();
Filters.Add(new DynamicModels.RootReportFilter {
FilterName = "EMAIL_", DBType = SqlDbType.VarChar, ParamSize = -1, ParamValue = _emailaddress
});
DataTable PASSWORD = DynamicOutput.DynoProcSearch(_Connect, "Custom Query", "SP_S_VW__ID_PASSWORD_SEARCH",
new DataTableDotNetModelMetaData {
columns = "PASSWORD, IDENTITIES_ID", length = -1, order = "1 asc", start = 0, verify = "T"
},
Filters);
DataColumnCollection _dccColumnID = PASSWORD.Columns;
if (_dccColumnID.Contains("IDENTITIES_ID") && PASSWORD.Rows.Count > 0)
{
long? IdentityId = PASSWORD.Rows[0].Field <long?>("IDENTITIES_ID");
Values.AddVerify thisVerify = addHelp.ADD_ENTRY_Verify(_Connect, new Values.AddVerify {
I_IDENTITIES_ID = IdentityId, I_VALIDATION_TYPE = "ResetPassword"
});
string VerifyUUID = thisVerify.I_UUID;
string Host = Current.Request.Scheme + "://" + Current.Request.Host.ToString().ToLower();
if (Current.Request.Host.Port != 80 &&
Current.Request.Host.Port != 443)
{
Host = Host + ":" + Current.Request.Host.Port;
}
string VerifyURL = Host + "/ResetPassword/Index/" + VerifyUUID;
string EmailBody = HttpUtility.HtmlEncode("This email was sent by an automated administrator. Please do not reply to this message.<br><br>This link will be active for 1 hour!" +
"<br /><br />" +
"Please click <a href='" + VerifyURL + "'>here</a> to reset your password.");
//"<br /><br />" +
//_Connect.SourceDBOwner + " Verification URL: " +
//"<br /><br />" +
//VerifyURL +
//"<br /><br /><br /><br />" +
//"Thanks for using Revamp!"
string tempemail = _emailaddress;
IOHelper ioHelper = new IOHelper();
try
{
string subject = "NO REPLY: Password reset link for " + _Connect.SourceDBOwner.ToUpper() + " ACCOUNT";
ioHelper.SendEmail(tempemail, subject, EmailBody);
}
catch (Exception e)
{
//Couldn't send email.
throw e;
}
return(true);
}
else
{
return(false);
}
}
private void _btn_datalog_to_matlab_Click(object sender, EventArgs e)
{
DataColumnCollection dc = _ds_logLines.Tables["Data"].Columns;
if (!dc.Contains("AccX") || !dc.Contains("GyroX") || !dc.Contains("SpeedGPS") ||
!dc.Contains("HeightBaro") || !dc.Contains("HeadingGPS") || !dc.Contains("Latitude"))
{
MessageBox.Show("Not all needed data available: Acc[XYZ], Gyro[XYZ], Latitude, Longitude, SpeedGPS, HeadingGPS, HeightBaro");
return;
}
string prefix = Microsoft.VisualBasic.Interaction.InputBox("Please enter the prefix for the files:", "Export to *.dat", "", 10, 20);
SaveFileDialog sfd = new SaveFileDialog();
System.Windows.Forms.FolderBrowserDialog f = new FolderBrowserDialog();
DialogResult r = f.ShowDialog();
if (r == DialogResult.OK)
{
StreamWriter f_acc_x = new StreamWriter(f.SelectedPath + "\\" +
prefix + "_raw_acc_x.dat");
StreamWriter f_acc_y = new StreamWriter(f.SelectedPath + "\\" +
prefix + "_raw_acc_y.dat");
StreamWriter f_acc_z = new StreamWriter(f.SelectedPath + "\\" +
prefix + "_raw_acc_z.dat");
StreamWriter f_gyro_x = new StreamWriter(f.SelectedPath + "\\" +
prefix + "_raw_gyro_x.dat");
StreamWriter f_gyro_y = new StreamWriter(f.SelectedPath + "\\" +
prefix + "_raw_gyro_y.dat");
StreamWriter f_gyro_z = new StreamWriter(f.SelectedPath + "\\" +
prefix + "_raw_gyro_z.dat");
StreamWriter f_speed_m_s = new StreamWriter(f.SelectedPath + "\\" +
prefix + "_speed_m_s.dat");
StreamWriter f_time_s = new StreamWriter(f.SelectedPath + "\\" +
prefix + "_time_s.dat");
StreamWriter f_longitude_deg = new StreamWriter(f.SelectedPath + "\\" +
prefix + "_longitude_deg.dat");
StreamWriter f_latitude_deg = new StreamWriter(f.SelectedPath + "\\" +
prefix + "_latitude_deg.dat");
StreamWriter f_heading_deg = new StreamWriter(f.SelectedPath + "\\" +
prefix + "_heading_deg.dat");
StreamWriter f_height_m = new StreamWriter(f.SelectedPath + "\\" +
prefix + "_height_m.dat");
foreach (DataRow dr in _ds_logLines.Tables["Data"].Rows)
{
f_acc_x.WriteLine(dr["AccX"]);
f_acc_y.WriteLine(dr["AccY"]);
f_acc_z.WriteLine(dr["AccZ"]);
f_gyro_x.WriteLine(dr["GyroX"]);
f_gyro_y.WriteLine(dr["GyroY"]);
f_gyro_z.WriteLine(dr["GyroZ"]);
f_speed_m_s.WriteLine(dr["SpeedGPS"]);
f_height_m.WriteLine(dr["HeightBaro"]);
f_heading_deg.WriteLine(dr["HeadingGPS"]);
f_latitude_deg.WriteLine(dr["Latitude"]);
f_longitude_deg.WriteLine(dr["Longitude"]);
if (dr.Table.Columns.Contains("Time"))
{
f_time_s.WriteLine(dr["Time"]);
}
}
f_acc_x.Close();
f_acc_y.Close();
f_acc_z.Close();
f_gyro_x.Close();
f_gyro_y.Close();
f_gyro_z.Close();
f_speed_m_s.Close();
f_height_m.Close();
f_heading_deg.Close();
f_latitude_deg.Close();
f_longitude_deg.Close();
f_time_s.Close();
}
}
/// <summary>
/// checks if edited fields exist in webDB and their types are adequate, checks constraints on FKs and mapping tables,
/// also checks if controls` dataTables` columns exist and everything that has to be inserted in DB is a required field,
/// whether attributes don`t colide and every panel has something to display.
/// </summary>
/// <param name="proposalPanel"></param>
/// <param name="recursive">run itself on panel children</param>
/// <returns>true if no errors were found, true otherwise</returns>
public bool checkPanelProposal(Panel proposalPanel, out List <string> errorMsgs, Dictionary <DataColumn, Dictionary <string, object> > customs)
// non-recursive checking after the initial check - after panel modification
{
errorMsgs = new List <string>();
DataColumnCollection cols = stats.ColumnTypes[proposalPanel.tableName];
List <FK> FKs = stats.FKs[proposalPanel.tableName];
List <M2NMapping> mappings = stats.Mappings[proposalPanel.tableName];
bool good = true;
if (proposalPanel.type == PanelTypes.Editable)
// this is indeed the only panelType containing fields => only editable
{
foreach (IField field in proposalPanel.fields)
{
if (!(field is IColumnField))
{
continue; // M2Ns are OK - strict UI
}
IColumnField cf = (IColumnField)field;
string messageBeginning = "Column " + cf.ColumnName + " managed by field " + cf.Caption + " ";
if (!(cols.Contains(cf.ColumnName)))
{
errorMsgs.Add(messageBeginning + "does not exist in table");
good = false;
}
else
{
if (!(cf is FKField)) // NavTable won`t be edited in the panel
{
if (cols[cf.ColumnName].AllowDBNull == false && // things that have to be filled in must be required
!cols[cf.ColumnName].AutoIncrement &&
!(cf is CheckboxField) &&
!cf.Required)
{
errorMsgs.Add(messageBeginning
+ "cannot be set to null, but the coresponding field is not required");
good = false;
}
// PBPR
// other validation requirements will be fullfilled based on the field type - the panel editation GUI will not let the user
// select a field type that is inconsistent with the column datatype
if (cols[cf.ColumnName].Unique && !cf.Unique)
{
errorMsgs.Add(messageBeginning + " is constrained to be unique, but the corresponding field does not have "
+ "the \"Unique\" validation rule set.");
good = false;
}
else if (cf.Unique && !cols[cf.ColumnName].Unique) // require uniqueness that is not guaranteed by the database
{
IBaseDriver tmpBaseDriver = null;
switch (CE.project.ServerType)
{
case DbServer.MySql:
tmpBaseDriver = new BaseDriverMySql(CE.project.ConnstringWeb);
break;
case DbServer.MsSql:
tmpBaseDriver = new BaseDriverMsSql(CE.project.ConnstringWeb);
break;
default:
break;
}
bool truelyUnique = tmpBaseDriver.CheckUniquenessManually(proposalPanel.tableName, cf.ColumnName);
if (!truelyUnique)
{
errorMsgs.Add(String.Format("{0} is not unique in its values - the constraint cannot be enforced.", messageBeginning));
good = false;
}
}
}
}
}
// Columns that are not covered and should be - allowdbnull is false, they do not have any default value
// and are not AutoIncrements and none of the fields refers to them.
var colsx = stats.ColumnTypes[proposalPanel.tableName];
IEnumerable <string> requiredColsMissing = from DataColumn col in stats.ColumnTypes[proposalPanel.tableName]
where col.AllowDBNull == false && col.AutoIncrement == false &&
(col.DefaultValue == null || col.DefaultValue == DBNull.Value) &&
!proposalPanel.fields.Exists(x => x is IColumnField && ((IColumnField)x).ColumnName == col.ColumnName) &&
(!customs.ContainsKey(col) || (bool)customs[col]["required"] == false)
select col.ColumnName;
// if the colum is contained in customs, only the custom factory validation will be left
//so this validation does not need to be called again
foreach (string missingCol in requiredColsMissing)
{
good = false;
errorMsgs.Add("Column " + missingCol + " cannot be NULL and has no default value." +
" It must therefore be included in the panel");
}
if (proposalPanel.panelName == "")
{
errorMsgs.Add("Does this panel not deserve a name?");
good = false;
}
if (proposalPanel.controls.Count == 0)
{
errorMsgs.Add("A panel with no controls would be useless...");
}
}
else
{
throw new Exception("Validation-non editable panel as an editable one.");
}
return(good);
}
public void bindgrid()
{
try
{
if (con.State == ConnectionState.Open)
{
con.Close();
}
con.Open();
totalnet = 0;
zeroper = 0;
taxableamt = 0;
taxamt = 0;
totalamt = 0;
LVclient.Items.Clear();
//column generation
#region
main = new DataTable();
main.Columns.Add("Bill NO", typeof(string));
main.Columns.Add("Date", typeof(string));
main.Columns.Add("Party Name", typeof(string));
main.Columns.Add("GST No.", typeof(string));
main.Columns.Add("Bill Amt", typeof(string));
main.Columns.Add("0%", typeof(string));
// DataTable invoicedt = conn.getdataset("select b.billno, b.bill_date,c.accountname,c.gstno,b.totalnet,b.totalbasic,b.totaltax from billmaster b inner join clientmaster c on c.clientid=b.clientid where b.isactive=1 and b.billtype='S' and c.isactive=1 and b.bill_date>='" + Convert.ToDateTime(DTPFrom.Text).ToString(Master.dateformate) + "' and b.bill_date<='" + Convert.ToDateTime(DTPTo.Text).ToString(Master.dateformate) + "' order by b.id");
DataTable invoicedt = conn.getdataset("select billno,bill_date, accountname,gstno,totalnet,totalbasic,totaltax from (select b.billno, b.bill_date,c.accountname,c.gstno,b.totalnet,b.totalbasic,b.totaltax from billmaster b inner join clientmaster c on c.clientid=b.clientid where b.isactive=1 and b.billtype='S' and c.isactive=1 and b.bill_date>='" + Convert.ToDateTime(DTPFrom.Text).ToString(Master.dateformate) + "' and b.bill_date<='" + Convert.ToDateTime(DTPTo.Text).ToString(Master.dateformate) + "' union all select b.billno,b.date as bill_date,c.AccountName,c.gstno,b.totalfinalamount as totalnet,b.totalbasic,b.totxltax as totaltax from tblgstvouchermaster b inner join ClientMaster c on c.ClientID=b.party where b.isactive=1 and b.billtype='S' and c.isactive=1 and b.date>='" + Convert.ToDateTime(DTPFrom.Text).ToString(Master.dateformate) + "' and b.date<='" + Convert.ToDateTime(DTPTo.Text).ToString(Master.dateformate) + "' )entries order by Bill_Date");
// DataTable items = conn.getdataset("select distinct sgstper,cgstper,igstper,addtaxper from billproductmaster where isactive=1 and billtype='S' and Bill_Run_Date>='" + Convert.ToDateTime(DTPFrom.Text).ToString(Master.dateformate) + "' and Bill_Run_Date<='" + Convert.ToDateTime(DTPTo.Text).ToString(Master.dateformate) + "' order by sgstper");
// DataTable items = conn.getdataset("select distinct convert(decimal(18,2), case when igstper>sgstper+cgstper then round(igstper/2,2) else sgstper end) as sgstper,convert(decimal(18,2),case when igstper>sgstper+cgstper then igstper/2 else cgstper end) as cgstper,convert(decimal(18,2),(case when igstper>sgstper+cgstper then round(igstper/2,2) else sgstper end )+(case when igstper>sgstper+cgstper then igstper/2 else cgstper end)) as igstper,addtaxper from billproductmaster where isactive=1 and billtype='S' and Bill_Run_Date>='" + Convert.ToDateTime(DTPFrom.Text).ToString(Master.dateformate) + "' and Bill_Run_Date<='" + Convert.ToDateTime(DTPTo.Text).ToString(Master.dateformate) + "' order by sgstper");
DataTable items = conn.getdataset("select distinct sgstper,cgstper,igstper,addtaxper from (select distinct convert(decimal(18,2), case when igstper>sgstper+cgstper then round(igstper/2,2) else sgstper end) as sgstper,convert(decimal(18,2),case when igstper>sgstper+cgstper then igstper/2 else cgstper end) as cgstper,convert(decimal(18,2),(case when igstper>sgstper+cgstper then round(igstper/2,2) else sgstper end )+(case when igstper>sgstper+cgstper then igstper/2 else cgstper end)) as igstper,addtaxper from billproductmaster where isactive=1 and billtype='S' and Bill_Run_Date>='" + Convert.ToDateTime(DTPFrom.Text).ToString(Master.dateformate) + "' and Bill_Run_Date<='" + Convert.ToDateTime(DTPTo.Text).ToString(Master.dateformate) + "' union all select distinct convert(decimal(18,2), case when igstper>sgstper+cgstper then round(igstper/2,2) else sgstper end) as sgstper,convert(decimal(18,2),case when igstper>sgstper+cgstper then igstper/2 else cgstper end) as cgstper,convert(decimal(18,2),(case when igstper>sgstper+cgstper then round(igstper/2,2) else sgstper end )+(case when igstper>sgstper+cgstper then igstper/2 else cgstper end)) as igstper,0 as addtaxper from tblgstvoucherproductmaster gp inner join tblgstvouchermaster g on g.id=gp.fkid where gp.isactive=1 and g.isactive=1 and gp.billtype='S' and g.billtype='S' and g.Date>='" + Convert.ToDateTime(DTPFrom.Text).ToString(Master.dateformate) + "' and g.Date<='" + Convert.ToDateTime(DTPTo.Text).ToString(Master.dateformate) + "' )entries order by cgstper");
string taxes = "", addtax = "";
for (int i = 0; i < items.Rows.Count; i++)
{
double tax = 0;
if (Convert.ToDouble(items.Rows[i]["igstper"].ToString()) > 0)
{
tax = Convert.ToDouble(items.Rows[i]["igstper"].ToString());
}
else
{
tax = Convert.ToDouble(items.Rows[i]["sgstper"].ToString()) + Convert.ToDouble(items.Rows[i]["cgstper"].ToString()) + Convert.ToDouble(items.Rows[i]["igstper"].ToString());
}
//if (Convert.ToDouble(items.Rows[i]["igstper"].ToString()) > (Convert.ToDouble(items.Rows[i]["sgstper"].ToString()) + Convert.ToDouble(items.Rows[i]["cgstper"].ToString())))
//{
// items.Rows[i]["cgstper"] = Math.Round(Convert.ToDouble(items.Rows[i]["igstper"].ToString()), 2) / 2;
// items.Rows[i]["sgstper"] = Math.Round(Convert.ToDouble(items.Rows[i]["igstper"].ToString()), 2) / 2;
// items.AcceptChanges();
//}
taxes += tax.ToString("N2") + ",";
DataColumnCollection columns = main.Columns;
if (!columns.Contains(tax.ToString("N2") + "% Amt."))
{
main.Columns.Add(tax.ToString("N2") + "% Amt.", typeof(string));
}
if (!columns.Contains("IGST " + tax.ToString("N2") + "%"))
{
main.Columns.Add("IGST " + tax.ToString("N2") + "%", typeof(string));
}
if (!columns.Contains("CGST " + items.Rows[i]["cgstper"].ToString() + "%"))
{
main.Columns.Add("CGST " + items.Rows[i]["cgstper"].ToString() + "%", typeof(string));
}
if (!columns.Contains("SGST " + items.Rows[i]["sgstper"].ToString() + "%"))
{
main.Columns.Add("SGST " + items.Rows[i]["sgstper"].ToString() + "%", typeof(string));
}
string p = tax.ToString("N2") + "% A.Tax " + items.Rows[i]["addtaxper"].ToString() + "%";
addtax += p + ",";
if (!columns.Contains(p))
{
main.Columns.Add(p, typeof(string));
}
}
// DataTable charge = conn.getdataset("select distinct tax,additax,addtaxamt from Billchargesmaster where isactive=1 and billtype='S'");
DataTable charge = conn.getdataset("select distinct tax,additax,addtaxamt from (select distinct tax,additax,addtaxamt from Billchargesmaster where isactive=1 and billtype='S'union all select distinct tax,additax,addtaxamt from tblgstvoucherchargesmaster where isactive=1 and billtype='S') entries order by tax");
string[] tx = taxes.Split(',');
string[] atx = addtax.Split(',');
for (int i = 0; i < charge.Rows.Count; i++)
{
int flag = 0;
for (int j = 0; j < tx.Length - 1; j++)
{
if (Convert.ToDouble(charge.Rows[i]["tax"].ToString()) == Convert.ToDouble(tx[j]))
{
flag = 1;
}
}
if (flag == 0 && Convert.ToDouble(charge.Rows[i]["tax"].ToString()) > 0)
{
main.Columns.Add(charge.Rows[i]["tax"].ToString() + "% Amt.", typeof(string));
main.Columns.Add("IGST " + charge.Rows[i]["tax"].ToString() + "%", typeof(string));
main.Columns.Add("CGST " + (Convert.ToDouble(charge.Rows[i]["tax"].ToString()) / 2).ToString("N2") + "%", typeof(string));
main.Columns.Add("SGST " + (Convert.ToDouble(charge.Rows[i]["tax"].ToString()) / 2).ToString("N2") + "%", typeof(string));
//string t=charge.Rows[i]["tax"].ToString() + "% A.Tax " + charge.Rows[i]["additax"].ToString() + "%";
//main.Columns.Add(t, typeof(string));
}
flag = 0;
for (int j = 0; j < atx.Length - 1; j++)
{
if (charge.Rows[i]["tax"].ToString() + "% A.Tax " + charge.Rows[i]["additax"].ToString() + "%" == atx[j])
{
flag = 1;
}
}
if (flag == 0 && Convert.ToDouble(charge.Rows[i]["tax"].ToString()) > 0)
{
string t = charge.Rows[i]["tax"].ToString() + "% A.Tax " + charge.Rows[i]["additax"].ToString() + "%";
main.Columns.Add(t, typeof(string));
}
}
main.Columns.Add("Taxable Amt", typeof(string));
main.Columns.Add("Tax Amt", typeof(string));
main.Columns.Add("Total Amt", typeof(string));
#endregion
//row generation
#region
for (int i = 0; i < invoicedt.Rows.Count; i++)
{
DataRow dr = main.NewRow();
string d = Convert.ToDateTime(invoicedt.Rows[i]["bill_date"]).ToString(Master.dateformate);
dr["Bill NO"] = invoicedt.Rows[i]["billno"].ToString();
dr["Date"] = d;
dr["Party Name"] = invoicedt.Rows[i]["accountname"].ToString();
dr["GST No."] = invoicedt.Rows[i]["gstno"].ToString();
dr["Bill Amt"] = invoicedt.Rows[i]["totalnet"].ToString();
totalnet = totalnet + Convert.ToDouble(invoicedt.Rows[i]["totalnet"].ToString());
dr["0%"] = "";
double taxable = 0, tax = 0, total = 0;
DataTable taxdetails = conn.getdataset("select sum(total) as basic,sgstper,sum(sgstamt) as sgstamt,cgstper,sum(cgstamt) as cgstamt,igstper,sum(igdtamt) as igstamt,addtaxper,sum(addtax) as addtax,sum(discountamt) as disamt from billproductmaster where billno='" + invoicedt.Rows[i]["billno"].ToString() + "' and Bill_Run_Date>='" + Convert.ToDateTime(DTPFrom.Text).ToString(Master.dateformate) + "' and Bill_Run_Date<='" + Convert.ToDateTime(DTPTo.Text).ToString(Master.dateformate) + "' and isactive=1 and billtype='S' group by sgstper,cgstper,igstper,addtaxper order by sgstper");
for (int j = 0; j < taxdetails.Rows.Count; j++)
{
string taxx = Math.Round(Convert.ToDouble(taxdetails.Rows[j]["sgstper"].ToString()) + Convert.ToDouble(taxdetails.Rows[j]["cgstper"].ToString()) + Convert.ToDouble(taxdetails.Rows[j]["igstper"].ToString()), 2).ToString("N2");
if (Convert.ToDouble(taxdetails.Rows[j]["sgstper"].ToString()) == 0 && Convert.ToDouble(taxdetails.Rows[j]["cgstper"].ToString()) == 0 && Convert.ToDouble(taxdetails.Rows[j]["igstper"].ToString()) == 0)
{
dr["0%"] = Math.Round((Convert.ToDouble(taxdetails.Rows[j]["basic"].ToString()) - Convert.ToDouble(taxdetails.Rows[j]["disamt"].ToString())), 2).ToString("N2");
taxable += Convert.ToDouble(dr["0%"]);
zeroper += Convert.ToDouble(dr["0%"].ToString());
}
else
{
dr[taxx + "% Amt."] = Math.Round(Convert.ToDouble(taxdetails.Rows[j]["basic"].ToString()) - Convert.ToDouble(taxdetails.Rows[j]["disamt"].ToString()), 2).ToString("N2");
taxable += Convert.ToDouble(dr[taxx + "% Amt."]);
dr["IGST " + taxx + "%"] = Math.Round(Convert.ToDouble(taxdetails.Rows[j]["igstamt"].ToString()), 2).ToString("N2");
tax += Convert.ToDouble(dr["IGST " + taxx + "%"]);
dr["CGST " + Math.Round(Convert.ToDouble(taxx) / 2, 2).ToString("N2") + "%"] = Math.Round(Convert.ToDouble(taxdetails.Rows[j]["cgstamt"].ToString()), 2).ToString("N2");
tax += Convert.ToDouble(dr["CGST " + Math.Round(Convert.ToDouble(taxx) / 2, 2).ToString("N2") + "%"]);
dr["SGST " + Math.Round(Convert.ToDouble(taxx) / 2, 2).ToString("N2") + "%"] = Math.Round(Convert.ToDouble(taxdetails.Rows[j]["sgstamt"].ToString()), 2).ToString("N2");
tax += Convert.ToDouble(dr["SGST " + Math.Round(Convert.ToDouble(taxx) / 2, 2).ToString("N2") + "%"]);
dr[taxx + "% A.Tax " + taxdetails.Rows[j]["addtaxper"].ToString() + "%"] = Math.Round(Convert.ToDouble(taxdetails.Rows[j]["addtax"].ToString()), 2).ToString("N2");
tax += Convert.ToDouble(dr[taxx + "% A.Tax " + taxdetails.Rows[j]["addtaxper"].ToString() + "%"]);
}
}
DataTable charges = conn.getdataset("select tax,plusminus,addtaxamt, ISNULL(sum(valueofexp),0.00) as value, ISNULL(sum(sgst),0.00) as sgst, ISNULL(sum(cgst),0.00) as cgst,ISNULL(sum(igst),0.00) as igst, ISNULL(sum(additax),0.00) as addtax from Billchargesmaster where billno='" + invoicedt.Rows[i]["billno"].ToString() + "' and isactive=1 and billtype='S' GROUP BY tax,plusminus,addtaxamt");
for (int j = 0; j < charges.Rows.Count; j++)
{
if (j == 0)
{
taxable = 0; tax = 0;
}
string taxx = charges.Rows[j]["tax"].ToString();
if (Convert.ToDouble(taxx) == 0)
{
// dr["0%"] = Math.Round(Convert.ToDouble(dr["0"]) + Convert.ToDouble(charges.Rows[0]["value"].ToString()), 2).ToString("N2");
}
else
{
try
{
string str = Convert.ToDouble(dr[taxx + "% Amt."]).ToString();
}
catch
{
dr[taxx + "% Amt."] = 0;
}
try
{
string str = Convert.ToDouble(dr["IGST " + taxx + "%"]).ToString();
}
catch
{
dr["IGST " + taxx + "%"] = 0;
}
try
{
string str = Convert.ToDouble(dr["CGST " + Math.Round(Convert.ToDouble(taxx) / 2, 2).ToString("N2") + "%"]).ToString();
}
catch
{
dr["CGST " + Math.Round(Convert.ToDouble(taxx) / 2, 2).ToString("N2") + "%"] = 0;
}
try
{
string str = Convert.ToDouble(dr["SGST " + Math.Round(Convert.ToDouble(taxx) / 2, 2).ToString("N2") + "%"]).ToString();
}
catch
{
dr["SGST " + Math.Round(Convert.ToDouble(taxx) / 2, 2).ToString("N2") + "%"] = 0;
}
try
{
string str = Convert.ToDouble(dr[taxx + "% A.Tax " + charges.Rows[j]["addtax"].ToString() + "%"]).ToString();
}
catch
{
dr[taxx + "% A.Tax " + charges.Rows[j]["addtax"].ToString() + "%"] = 0;
}
if (charges.Rows[j]["plusminus"].ToString() == "+")
{
dr[taxx + "% Amt."] = Math.Round(Convert.ToDouble(dr[taxx + "% Amt."]) + Convert.ToDouble(charges.Rows[j]["value"].ToString()), 2).ToString("N2");
}
else
{
dr[taxx + "% Amt."] = Math.Round(Convert.ToDouble(dr[taxx + "% Amt."]) - Convert.ToDouble(charges.Rows[j]["value"].ToString()), 2).ToString("N2");
}
taxable += Convert.ToDouble(dr[taxx + "% Amt."]);
if (charges.Rows[j]["plusminus"].ToString() == "+")
{
dr["IGST " + taxx + "%"] = Math.Round(Convert.ToDouble(dr["IGST " + taxx + "%"]) + Convert.ToDouble(charges.Rows[j]["igst"].ToString()), 2).ToString("N2");
}
else
{
dr["IGST " + taxx + "%"] = Math.Round(Convert.ToDouble(dr["IGST " + taxx + "%"]) - Convert.ToDouble(charges.Rows[j]["igst"].ToString()), 2).ToString("N2");
}
tax += Convert.ToDouble(dr["IGST " + taxx + "%"]);
if (charges.Rows[j]["plusminus"].ToString() == "+")
{
dr["CGST " + Math.Round(Convert.ToDouble(taxx) / 2, 2).ToString("N2") + "%"] = Math.Round(Convert.ToDouble(dr["CGST " + Math.Round(Convert.ToDouble(taxx) / 2, 2).ToString("N2") + "%"]) + Convert.ToDouble(charges.Rows[j]["cgst"].ToString()), 2).ToString("N2");
}
else
{
dr["CGST " + Math.Round(Convert.ToDouble(taxx) / 2, 2).ToString("N2") + "%"] = Math.Round(Convert.ToDouble(dr["CGST " + Math.Round(Convert.ToDouble(taxx) / 2, 2).ToString("N2") + "%"]) - Convert.ToDouble(charges.Rows[j]["cgst"].ToString()), 2).ToString("N2");
}
tax += Convert.ToDouble(dr["CGST " + Math.Round(Convert.ToDouble(taxx) / 2, 2).ToString("N2") + "%"]);
if (charges.Rows[j]["plusminus"].ToString() == "+")
{
dr["SGST " + Math.Round(Convert.ToDouble(taxx) / 2, 2).ToString("N2") + "%"] = Math.Round(Convert.ToDouble(dr["SGST " + Math.Round(Convert.ToDouble(taxx) / 2, 2).ToString("N2") + "%"]) + Convert.ToDouble(charges.Rows[j]["sgst"].ToString()), 2).ToString("N2");
}
else
{
dr["SGST " + Math.Round(Convert.ToDouble(taxx) / 2, 2).ToString("N2") + "%"] = Math.Round(Convert.ToDouble(dr["SGST " + Math.Round(Convert.ToDouble(taxx) / 2, 2).ToString("N2") + "%"]) - Convert.ToDouble(charges.Rows[j]["sgst"].ToString()), 2).ToString("N2");
}
tax += Convert.ToDouble(dr["SGST " + Math.Round(Convert.ToDouble(taxx) / 2, 2).ToString("N2") + "%"]);
if (charges.Rows[j]["plusminus"].ToString() == "+")
{
dr[taxx + "% A.Tax " + charges.Rows[j]["addtax"].ToString() + "%"] = Math.Round(Convert.ToDouble(dr[taxx + "% A.Tax " + charges.Rows[j]["addtax"].ToString() + "%"]) + Convert.ToDouble(charges.Rows[j]["addtaxamt"].ToString()), 2).ToString("N2");
}
else
{
dr[taxx + "% A.Tax " + charges.Rows[j]["addtax"].ToString() + "%"] = Math.Round(Convert.ToDouble(dr[taxx + "% A.Tax " + charges.Rows[j]["addtax"].ToString() + "%"]) - Convert.ToDouble(charges.Rows[j]["addtaxamt"].ToString()), 2).ToString("N2");
}
tax += Convert.ToDouble(dr[taxx + "% A.Tax " + charges.Rows[j]["addtax"].ToString() + "%"]);
}
}
DataTable taxdetails1 = conn.getdataset("select sum(gp.taxableamount) as basic,gp.sgstper,sum(gp.sgstamt) as sgstamt,gp.cgstper,sum(gp.cgstamt) as cgstamt,gp.igstper,sum(gp.igstamt) as igstamt,0 as addtaxper,sum(gp.addtax) as addtax,0 as disamt from tblgstvoucherproductmaster gp inner join tblgstvouchermaster g on g.id=gp.fkid where gp.billno='" + invoicedt.Rows[i]["billno"].ToString() + "' and g.Date>='" + Convert.ToDateTime(DTPFrom.Text).ToString(Master.dateformate) + "' and g.Date<='" + Convert.ToDateTime(DTPTo.Text).ToString(Master.dateformate) + "' and gp.isactive=1 and gp.billtype='S' and g.isactive=1 and g.billtype='S'group by sgstper,cgstper,igstper");
for (int j = 0; j < taxdetails1.Rows.Count; j++)
{
string taxx = Math.Round(Convert.ToDouble(taxdetails1.Rows[j]["sgstper"].ToString()) + Convert.ToDouble(taxdetails1.Rows[j]["cgstper"].ToString()) + Convert.ToDouble(taxdetails1.Rows[j]["igstper"].ToString()), 2).ToString("N2");
if (Convert.ToDouble(taxdetails1.Rows[j]["sgstper"].ToString()) == 0 && Convert.ToDouble(taxdetails1.Rows[j]["cgstper"].ToString()) == 0 && Convert.ToDouble(taxdetails1.Rows[j]["igstper"].ToString()) == 0)
{
dr["0%"] = Math.Round((Convert.ToDouble(taxdetails1.Rows[j]["basic"].ToString()) - Convert.ToDouble(taxdetails1.Rows[j]["disamt"].ToString())), 2).ToString("N2");
taxable += Convert.ToDouble(dr["0%"]);
zeroper += Convert.ToDouble(dr["0%"].ToString());
}
else
{
dr[taxx + "% Amt."] = Math.Round(Convert.ToDouble(taxdetails1.Rows[j]["basic"].ToString()) - Convert.ToDouble(taxdetails1.Rows[j]["disamt"].ToString()), 2).ToString("N2");
taxable += Convert.ToDouble(dr[taxx + "% Amt."]);
dr["IGST " + taxx + "%"] = Math.Round(Convert.ToDouble(taxdetails1.Rows[j]["igstamt"].ToString()), 2).ToString("N2");
tax += Convert.ToDouble(dr["IGST " + taxx + "%"]);
dr["CGST " + Math.Round(Convert.ToDouble(taxx) / 2, 2).ToString("N2") + "%"] = Math.Round(Convert.ToDouble(taxdetails1.Rows[j]["cgstamt"].ToString()), 2).ToString("N2");
tax += Convert.ToDouble(dr["CGST " + Math.Round(Convert.ToDouble(taxx) / 2, 2).ToString("N2") + "%"]);
dr["SGST " + Math.Round(Convert.ToDouble(taxx) / 2, 2).ToString("N2") + "%"] = Math.Round(Convert.ToDouble(taxdetails1.Rows[j]["sgstamt"].ToString()), 2).ToString("N2");
tax += Convert.ToDouble(dr["SGST " + Math.Round(Convert.ToDouble(taxx) / 2, 2).ToString("N2") + "%"]);
dr[taxx + "% A.Tax " + taxdetails1.Rows[j]["addtaxper"].ToString() + "%"] = Math.Round(Convert.ToDouble(taxdetails1.Rows[j]["addtax"].ToString()), 2).ToString("N2");
tax += Convert.ToDouble(dr[taxx + "% A.Tax " + taxdetails1.Rows[j]["addtaxper"].ToString() + "%"]);
}
}
DataTable charges1 = conn.getdataset("select tax,plusminus,addtaxamt, ISNULL(sum(valueofexp),0.00) as value, ISNULL(sum(sgst),0.00) as sgst, ISNULL(sum(cgst),0.00) as cgst,ISNULL(sum(igst),0.00) as igst, ISNULL(sum(additax),0.00) as addtax from tblgstvoucherchargesmaster where billno='" + invoicedt.Rows[i]["billno"].ToString() + "' and isactive=1 and billtype='S' GROUP BY tax,plusminus,addtaxamt");
for (int j = 0; j < charges1.Rows.Count; j++)
{
if (j == 0)
{
taxable = 0; tax = 0;
}
string taxx = charges1.Rows[j]["tax"].ToString();
if (Convert.ToDouble(taxx) == 0)
{
// dr["0%"] = Math.Round(Convert.ToDouble(dr["0"]) + Convert.ToDouble(charges.Rows[0]["value"].ToString()), 2).ToString("N2");
}
else
{
try
{
string str = Convert.ToDouble(dr[taxx + "% Amt."]).ToString();
}
catch
{
dr[taxx + "% Amt."] = 0;
}
try
{
string str = Convert.ToDouble(dr["IGST " + taxx + "%"]).ToString();
}
catch
{
dr["IGST " + taxx + "%"] = 0;
}
try
{
string str = Convert.ToDouble(dr["CGST " + Math.Round(Convert.ToDouble(taxx) / 2, 2).ToString("N2") + "%"]).ToString();
}
catch
{
dr["CGST " + Math.Round(Convert.ToDouble(taxx) / 2, 2).ToString("N2") + "%"] = 0;
}
try
{
string str = Convert.ToDouble(dr["SGST " + Math.Round(Convert.ToDouble(taxx) / 2, 2).ToString("N2") + "%"]).ToString();
}
catch
{
dr["SGST " + Math.Round(Convert.ToDouble(taxx) / 2, 2).ToString("N2") + "%"] = 0;
}
try
{
string str = Convert.ToDouble(dr[taxx + "% A.Tax " + charges1.Rows[j]["addtax"].ToString() + "%"]).ToString();
}
catch
{
dr[taxx + "% A.Tax " + charges1.Rows[j]["addtax"].ToString() + "%"] = 0;
}
if (charges1.Rows[j]["plusminus"].ToString() == "+")
{
dr[taxx + "% Amt."] = Math.Round(Convert.ToDouble(dr[taxx + "% Amt."]) + Convert.ToDouble(charges1.Rows[j]["value"].ToString()), 2).ToString("N2");
}
else
{
dr[taxx + "% Amt."] = Math.Round(Convert.ToDouble(dr[taxx + "% Amt."]) - Convert.ToDouble(charges1.Rows[j]["value"].ToString()), 2).ToString("N2");
}
taxable += Convert.ToDouble(dr[taxx + "% Amt."]);
if (charges1.Rows[j]["plusminus"].ToString() == "+")
{
dr["IGST " + taxx + "%"] = Math.Round(Convert.ToDouble(dr["IGST " + taxx + "%"]) + Convert.ToDouble(charges1.Rows[j]["igst"].ToString()), 2).ToString("N2");
}
else
{
dr["IGST " + taxx + "%"] = Math.Round(Convert.ToDouble(dr["IGST " + taxx + "%"]) - Convert.ToDouble(charges1.Rows[j]["igst"].ToString()), 2).ToString("N2");
}
tax += Convert.ToDouble(dr["IGST " + taxx + "%"]);
if (charges1.Rows[j]["plusminus"].ToString() == "+")
{
dr["CGST " + Math.Round(Convert.ToDouble(taxx) / 2, 2).ToString("N2") + "%"] = Math.Round(Convert.ToDouble(dr["CGST " + Math.Round(Convert.ToDouble(taxx) / 2, 2).ToString("N2") + "%"]) + Convert.ToDouble(charges1.Rows[j]["cgst"].ToString()), 2).ToString("N2");
}
else
{
dr["CGST " + Math.Round(Convert.ToDouble(taxx) / 2, 2).ToString("N2") + "%"] = Math.Round(Convert.ToDouble(dr["CGST " + Math.Round(Convert.ToDouble(taxx) / 2, 2).ToString("N2") + "%"]) - Convert.ToDouble(charges1.Rows[j]["cgst"].ToString()), 2).ToString("N2");
}
tax += Convert.ToDouble(dr["CGST " + Math.Round(Convert.ToDouble(taxx) / 2, 2).ToString("N2") + "%"]);
if (charges1.Rows[j]["plusminus"].ToString() == "+")
{
dr["SGST " + Math.Round(Convert.ToDouble(taxx) / 2, 2).ToString("N2") + "%"] = Math.Round(Convert.ToDouble(dr["SGST " + Math.Round(Convert.ToDouble(taxx) / 2, 2).ToString("N2") + "%"]) + Convert.ToDouble(charges1.Rows[j]["sgst"].ToString()), 2).ToString("N2");
}
else
{
dr["SGST " + Math.Round(Convert.ToDouble(taxx) / 2, 2).ToString("N2") + "%"] = Math.Round(Convert.ToDouble(dr["SGST " + Math.Round(Convert.ToDouble(taxx) / 2, 2).ToString("N2") + "%"]) - Convert.ToDouble(charges1.Rows[j]["sgst"].ToString()), 2).ToString("N2");
}
tax += Convert.ToDouble(dr["SGST " + Math.Round(Convert.ToDouble(taxx) / 2, 2).ToString("N2") + "%"]);
if (charges1.Rows[j]["plusminus"].ToString() == "+")
{
dr[taxx + "% A.Tax " + charges1.Rows[j]["addtax"].ToString() + "%"] = Math.Round(Convert.ToDouble(dr[taxx + "% A.Tax " + charges1.Rows[j]["addtax"].ToString() + "%"]) + Convert.ToDouble(charges1.Rows[j]["addtaxamt"].ToString()), 2).ToString("N2");
}
else
{
dr[taxx + "% A.Tax " + charges1.Rows[j]["addtax"].ToString() + "%"] = Math.Round(Convert.ToDouble(dr[taxx + "% A.Tax " + charges1.Rows[j]["addtax"].ToString() + "%"]) - Convert.ToDouble(charges1.Rows[j]["addtaxamt"].ToString()), 2).ToString("N2");
}
tax += Convert.ToDouble(dr[taxx + "% A.Tax " + charges1.Rows[j]["addtax"].ToString() + "%"]);
}
}
dr["Taxable Amt"] = taxable.ToString("N2");
dr["Tax Amt"] = tax.ToString("N2");
dr["Total Amt"] = (taxable + tax).ToString("N2");
main.Rows.Add(dr);
}
Double[] tot = new Double[main.Columns.Count];
for (int i = 0; i < main.Rows.Count; i++)
{
for (int j = 6; j < main.Columns.Count; j++)
{
if (main.Rows[i][j].ToString() == "")
{
tot[j] += Convert.ToDouble("0");
}
else
{
tot[j] += Convert.ToDouble(main.Rows[i][j].ToString());
}
}
}
DataRow lastdr = main.NewRow();
lastdr[0] = "";
lastdr[1] = "";
lastdr[2] = "";
lastdr[3] = "";
lastdr[4] = totalnet.ToString("N2");
lastdr[5] = zeroper.ToString("N2");
for (int i = 6; i < main.Columns.Count; i++)
{
if (i == 6)
{
lastdr[i] = tot[i].ToString("N2");
}
else
{
lastdr[i] = tot[i].ToString("N2");
}
}
main.Rows.Add();
main.Rows.Add(lastdr);
#endregion
bill = 0;
LVclient.Items.Clear();
int ColCount = main.Columns.Count;
//Add columns
for (int k = 0; k < ColCount; k++)
{
LVclient.Columns.Add(main.Columns[k].ColumnName, 120);
}
// Display items in the ListView control
for (int i = 0; i < main.Rows.Count; i++)
{
DataRow drow = main.Rows[i];
// Only row that have not been deleted
if (drow.RowState != DataRowState.Deleted)
{
// Define the list items
ListViewItem lvi = new ListViewItem(drow[0].ToString());
for (int j = 1; j < ColCount; j++)
{
lvi.SubItems.Add(drow[j].ToString());
}
// Add the list items to the ListView
LVclient.Items.Add(lvi);
}
}
}
catch (Exception ex)
{
}
finally
{
con.Close();
}
}
private string GetZScoreDetails(int ptn_pk, int visitPK, int locationId)
{
string result = string.Empty;
try
{
IClinicalEncounter clinicalencounter = (IClinicalEncounter)ObjectFactory.CreateInstance("HIVCE.BusinessLayer.BLClinicalEncounter, HIVCE.BusinessLayer");
HIVCE.Common.Entities.ClinicalEncounter cl = clinicalencounter.GetData(ptn_pk, visitPK, locationId);
DataSet ZScoreDS = new DataSet();
IKNHStaticForms KNHS = (IKNHStaticForms)ObjectFactory.CreateInstance("BusinessProcess.Clinical.BKNHStaticForms, BusinessProcess.Clinical");
string height = string.IsNullOrEmpty(cl.PatientVitals.Height) == true ? "0" : cl.PatientVitals.Height;
ZScoreDS = KNHS.GetZScoreNewImplementation(ptn_pk, Session["PatientSex"].ToString(), height.ToString());
ZScoreDetails zs = new ZScoreDetails();
zs.WFA = new ZScore();
zs.WFH = new ZScore();
zs.BMIz = new ZScore();
if (ZScoreDS.Tables[0].Rows.Count > 0)
{
DataColumnCollection columns = ZScoreDS.Tables[0].Columns;
if (columns.Contains("L"))
{
zs.WFA.L = Convert.ToDouble(ZScoreDS.Tables[0].Rows[0]["L"].ToString());
zs.WFA.M = Convert.ToDouble(ZScoreDS.Tables[0].Rows[0]["M"].ToString());
zs.WFA.S = Convert.ToDouble(ZScoreDS.Tables[0].Rows[0]["S"].ToString());
}
}
if (ZScoreDS.Tables[1].Rows.Count > 0)
{
DataColumnCollection columns = ZScoreDS.Tables[1].Columns;
if (columns.Contains("L"))
{
zs.WFH.L = Convert.ToDouble(ZScoreDS.Tables[1].Rows[0]["L"].ToString());
zs.WFH.M = Convert.ToDouble(ZScoreDS.Tables[1].Rows[0]["M"].ToString());
zs.WFH.S = Convert.ToDouble(ZScoreDS.Tables[1].Rows[0]["S"].ToString());
}
}
if (ZScoreDS.Tables[2].Rows.Count > 0)
{
DataColumnCollection columns = ZScoreDS.Tables[2].Columns;
if (columns.Contains("L"))
{
zs.BMIz.L = Convert.ToDouble(ZScoreDS.Tables[2].Rows[0]["L"].ToString());
zs.BMIz.M = Convert.ToDouble(ZScoreDS.Tables[2].Rows[0]["M"].ToString());
zs.BMIz.S = Convert.ToDouble(ZScoreDS.Tables[2].Rows[0]["S"].ToString());
}
}
ZScoreDS.Dispose();
result = SerializerUtil.ConverToJson <HIVCE.Common.Entities.ZScoreDetails>(zs);
}
catch (Exception ex)
{
CLogger.WriteLog(ELogLevel.ERROR, "GetZScoreDetails() exception: " + ex.ToString());
ResponseType response = new ResponseType()
{
Success = EnumUtil.GetEnumDescription(Success.False)
};
result = SerializerUtil.ConverToJson <ResponseType>(response);
}
finally
{
}
return(result);
}
protected void BtnExportarAExcel_Click(object sender, EventArgs e)
{
try
{
if (Session["dsReporteRemitosFaltantes"] != null)
{
List <RemitosFaltantesClaseReporte> lstRemitosFaltantesAll = new List <RemitosFaltantesClaseReporte>();
lstRemitosFaltantesAll = (List <RemitosFaltantesClaseReporte>)Session["dsReporteRemitosFaltantes"];
if (lstRemitosFaltantesAll.Count > 0)
{
PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(typeof(RemitosFaltantesClaseReporte));
DataTable table = new DataTable();
foreach (PropertyDescriptor prop in properties)
{
table.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType);
}
foreach (RemitosFaltantesClaseReporte item in lstRemitosFaltantesAll)
{
DataRow row = table.NewRow();
foreach (PropertyDescriptor prop in properties)
{
row[prop.Name] = prop.GetValue(item) ?? DBNull.Value;
}
table.Rows.Add(row);
}
DataSet dsExcel = new DataSet();
dsExcel.Tables.Add(table);
dsExcel.Tables[0].Columns["FechaRendicion"].SetOrdinal(0);
dsExcel.Tables[0].Columns["Comentario"].SetOrdinal(1);
dsExcel.Tables[0].Columns["FechaCarga"].SetOrdinal(2);
dsExcel.Tables[0].Columns["RazonSocial"].SetOrdinal(3);
dsExcel.Tables[0].Columns["Remito"].SetOrdinal(4);
dsExcel.Tables[0].Columns["FechaEmision"].SetOrdinal(5);
dsExcel.Tables[0].Columns["FacturaCliente"].SetOrdinal(6);
dsExcel.Tables[0].Columns["NroGuia"].SetOrdinal(7);
dsExcel.Tables[0].Columns["RazonSocialOrigen"].SetOrdinal(8);
dsExcel.Tables[0].Columns["RazonSocialDestino"].SetOrdinal(9);
dsExcel.Tables[0].Columns["UnidadVentaDescrip"].SetOrdinal(10);
dsExcel.Tables[0].Columns["ModalidadEntregaDescrip"].SetOrdinal(11);
dsExcel.Tables[0].Columns["Destinatario"].SetOrdinal(12);
dsExcel.Tables[0].Columns["EstadoAdministrativo"].SetOrdinal(13);
dsExcel.Tables[0].Columns["FechaEstadoAdministrativo"].SetOrdinal(14);
dsExcel.Tables[0].Columns["EstadoMovimiento"].SetOrdinal(15);
dsExcel.Tables[0].Columns["FechaEstadoMovimiento"].SetOrdinal(16);
dsExcel.Tables[0].Columns["EstadoUbicacion"].SetOrdinal(17);
dsExcel.Tables[0].Columns["FechaEstadoUbicacion"].SetOrdinal(18);
dsExcel.Tables[0].Columns["EstadoEntrega"].SetOrdinal(19);
dsExcel.Tables[0].Columns["FechaEstadoEntrega"].SetOrdinal(20);
dsExcel.Tables[0].Columns["Observaciones"].SetOrdinal(21);
dsExcel.Tables[0].Columns["RendicionConformacion"].SetOrdinal(22);
dsExcel.Tables[0].Columns["GuiaDigitalizada"].SetOrdinal(23);
dsExcel.Tables[0].Columns["RemitoDigitalizado"].SetOrdinal(24);
dsExcel.Tables[0].Columns["PesoTotal"].SetOrdinal(25);
dsExcel.Tables[0].Columns["CantidadBultosTotal"].SetOrdinal(26);
dsExcel.Tables[0].Columns["ImporteNetoTotal"].SetOrdinal(27);
DataGrid dg = new DataGrid();
dg.ShowFooter = false;
dg.ItemDataBound += new System.Web.UI.WebControls.DataGridItemEventHandler(dgReporteRemitosFaltantes_ItemDataBound);
DataColumnCollection columns = dsExcel.Tables[0].Columns;
if (columns.Contains("Rinde"))
{
dsExcel.Tables[0].Columns.Remove("Rinde");
}
if (columns.Contains("GuiaID"))
{
dsExcel.Tables[0].Columns.Remove("GuiaID");
}
if (columns.Contains("FacturaID"))
{
dsExcel.Tables[0].Columns.Remove("FacturaID");
}
if (columns.Contains("FacturacionBolsinesID"))
{
dsExcel.Tables[0].Columns.Remove("FacturacionBolsinesID");
}
if (columns.Contains("CodigoCliente"))
{
dsExcel.Tables[0].Columns.Remove("CodigoCliente");
}
if (columns.Contains("RemitoClienteNro"))
{
dsExcel.Tables[0].Columns.Remove("RemitoClienteNro");
}
if (columns.Contains("FechaCarga"))
{
dsExcel.Tables[0].Columns["FechaCarga"].ColumnName = "Fecha Carga";
}
if (columns.Contains("RazonSocial"))
{
dsExcel.Tables[0].Columns["RazonSocial"].ColumnName = "Cliente";
}
if (columns.Contains("FechaEmision"))
{
dsExcel.Tables[0].Columns["FechaEmision"].ColumnName = "Fecha Emisión";
}
if (columns.Contains("FacturaCliente"))
{
dsExcel.Tables[0].Columns["FacturaCliente"].ColumnName = "Factura Cliente";
}
if (columns.Contains("NroGuia"))
{
dsExcel.Tables[0].Columns["NroGuia"].ColumnName = "Nro. Guía";
}
if (columns.Contains("RazonSocialOrigen"))
{
dsExcel.Tables[0].Columns["RazonSocialOrigen"].ColumnName = "Agencia Origen";
}
if (columns.Contains("RazonSocialDestino"))
{
dsExcel.Tables[0].Columns["RazonSocialDestino"].ColumnName = "Agencia Destino";
}
if (columns.Contains("UnidadVentaDescrip"))
{
dsExcel.Tables[0].Columns["UnidadVentaDescrip"].ColumnName = "Unidad Venta";
}
if (columns.Contains("ModalidadEntregaDescrip"))
{
dsExcel.Tables[0].Columns["ModalidadEntregaDescrip"].ColumnName = "Modalidad Entrega";//
}
if (columns.Contains("EstadoAdministrativo"))
{
dsExcel.Tables[0].Columns["EstadoAdministrativo"].ColumnName = "Estado Administrativo";
}
if (columns.Contains("FechaEstadoAdministrativo"))
{
dsExcel.Tables[0].Columns["FechaEstadoAdministrativo"].ColumnName = "Fecha Estado Administrativo";
}
if (columns.Contains("EstadoMovimiento"))
{
dsExcel.Tables[0].Columns["EstadoMovimiento"].ColumnName = "Estado Movimiento";//
}
if (columns.Contains("FechaEstadoMovimiento"))
{
dsExcel.Tables[0].Columns["FechaEstadoMovimiento"].ColumnName = "Fecha Estado Movimiento";
}
if (columns.Contains("EstadoUbicacion"))
{
dsExcel.Tables[0].Columns["EstadoUbicacion"].ColumnName = "Estado Ubicacion";//
}
if (columns.Contains("FechaEstadoUbicacion"))
{
dsExcel.Tables[0].Columns["FechaEstadoUbicacion"].ColumnName = "Fecha Estado Ubicacion";
}
if (columns.Contains("EstadoEntrega"))
{
dsExcel.Tables[0].Columns["EstadoEntrega"].ColumnName = "Estado Entrega";//
}
if (columns.Contains("FechaEstadoEntrega"))
{
dsExcel.Tables[0].Columns["FechaEstadoEntrega"].ColumnName = "Fecha Estado Entrega";
}
if (columns.Contains("RendicionConformacion"))
{
dsExcel.Tables[0].Columns["RendicionConformacion"].ColumnName = "Rendicion Conformacion";//
}
if (columns.Contains("GuiaDigitalizada"))
{
dsExcel.Tables[0].Columns["GuiaDigitalizada"].ColumnName = "Guia Digitalizada";
}
if (columns.Contains("RemitoDigitalizado"))
{
dsExcel.Tables[0].Columns["RemitoDigitalizado"].ColumnName = "Remito Digitalizado";
}
if (columns.Contains("PesoTotal"))
{
dsExcel.Tables[0].Columns["PesoTotal"].ColumnName = "Peso Total";//
}
if (columns.Contains("CantidadBultosTotal"))
{
dsExcel.Tables[0].Columns["CantidadBultosTotal"].ColumnName = "Bultos";
}
if (columns.Contains("ImporteNetoTotal"))
{
dsExcel.Tables[0].Columns["ImporteNetoTotal"].ColumnName = "Importe NETO";
}
dsExcel.AcceptChanges();
dg.DataSource = dsExcel.Tables[0];
dg.DataBind();
this.EnableViewState = false;
Response.Clear();
Response.Buffer = true;
Response.ContentType = "application/vnd.ms-excel";
Response.Charset = "";
Response.ContentEncoding = System.Text.Encoding.Default;
Response.AddHeader("Content-Disposition", "filename=ReporteRemitosFaltantes.xls");
System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
dg.RenderControl(oHtmlTextWriter);
Response.Write(oStringWriter.ToString());
Response.End();
}
}
else
{
return;
}
}
catch (Exception ex)
{
throw ex;
}
}
// The Significance method is used to calculate the signficance of a change in number of result codes and it uses the total number of records for each
// country to calulate it
public DataTable Significance(DataTable data, string path, double signif, string tableName)
{
DataTable storage = new DataTable();
storage.Columns.Add("Country Codes", typeof(string));
// Pulls the country codes and totals to be used to calculate the basis for significance
string[] countries = GetCountry(path, tableName).Split(',');
string[] totals = GetTotal(path, tableName).Split(',');
// Iterates through the rows
for (int i = 0; i < data.Rows.Count; i++)
{
// Iterates through each column in the row
for (int j = 0; j < data.Columns.Count; j++)
{
// If the country in the row matches the country from the text file then it continues to calculate the significance
if (countries[j] == data.Rows[i][0].ToString())
{
int k = 4;
int l = 2;
if (k < data.Columns.Count)
{
// Compares two count columns from the database that was created and divides them by the total for the country the columns are pulled from
// to calculate if the significance falls within the range specified by the signif value that is an argument of the method
if ((double.Parse(data.Rows[i][l].ToString()) / double.Parse(totals[j])) - (double.Parse(data.Rows[i][k].ToString()) / double.Parse(totals[j]))
> (signif / 100) || ((double.Parse(data.Rows[i][k].ToString()) / double.Parse(totals[j])) - (double.Parse(data.Rows[i][l].ToString()) / double.Parse(totals[j]))
> (signif / 100)))
{
int m = 2;
DataColumnCollection columns = storage.Columns;
string country = data.Rows[i][0].ToString();
DataRow dr = storage.NewRow();
dr["Country Codes"] = country;
while (m < data.Columns.Count)
{
// For every column that is significant, it creates a sister column with the same name in a new data table
// along with the sister column of the result codes for the count column that was significant
if (!columns.Contains(data.Columns[m].ColumnName))
{
storage.Columns.Add(data.Columns[m - 1].ColumnName, typeof(string));
storage.Columns.Add(data.Columns[m].ColumnName, typeof(string));
// Adds each new column to a data row that will be later added to the new data table
dr[data.Columns[m - 1].ColumnName] = data.Rows[i][m].ToString();
dr[data.Columns[m].ColumnName] = data.Rows[i][m].ToString();
m += 2;
}
}
storage.Rows.Add(dr);
}
k += 2;
l += 2;
}
}
}
}
// Returns a data table that contains all of the significant changes from the result data table
return(storage);
}
public byte[] GetProfileImageBytes(IConnectToDB _Connect, long?identity_id)
{
if (identity_id != null)
{
byte[] value = new byte[0];
_DynamicOutputProcedures DynamicOutput = new _DynamicOutputProcedures();
List <DynamicModels.RootReportFilter> Filters = new List <DynamicModels.RootReportFilter>();
DataTable SetInfo = DynamicOutput.DynoProcSearch(_Connect, "Custom Query", "SP_S_VW_SET_INFO_SEARCH",
new DataTableDotNetModelMetaData {
length = -1, order = "1 asc", start = 0, verify = "T"
},
Filters);
long?_stages_id = 0;
long?_grips_id = 0;
long?_objectsets_id = 0;
long?_propset_id = 0;
foreach (DataRow _DR in SetInfo.Rows)
{
_stages_id = _DR.Field <long?>("stages_id");
_grips_id = _DR.Field <long?>("grips_id");
_objectsets_id = _DR.Field <long?>("object_sets_id");
_propset_id = _DR.Field <long?>("obj_prop_sets_id");
break;
}
List <DynamicModels.RootReportFilter> profileDatFileFilters = new List <DynamicModels.RootReportFilter>();
profileDatFileFilters.Add(new DynamicModels.RootReportFilter {
FilterName = "IDENTITIES_ID_", DBType = SqlDbType.BigInt, ParamValue = identity_id
});
//profileDatFileFilters.Add(new DynamicModels.RootReportFilter { FilterName = "STAGES_ID_", DBType = SqlDbType.BigInt, ParamValue = _stages_id });
//profileDatFileFilters.Add(new DynamicModels.RootReportFilter { FilterName = "GRIPS_ID_", DBType = SqlDbType.BigInt, ParamValue = _grips_id });
//profileDatFileFilters.Add(new DynamicModels.RootReportFilter { FilterName = "OBJ_PROP_SETS_ID_", DBType = SqlDbType.BigInt, ParamValue = _propset_id });
profileDatFileFilters.Add(new DynamicModels.RootReportFilter {
FilterName = "ENABLED_", DBType = SqlDbType.VarChar, ParamSize = -1, ParamValue = "Y"
});
DataTable TempDataTable = DynamicOutput.DynoProcSearch(_Connect, "Custom Query", "SP_S_VW__PROFILE_IMAGES_SEARCH",
new DataTableDotNetModelMetaData {
columns = "PROFILE_IMAGES_ID, VALUE", length = -1, order = "PROFILE_IMAGES_ID desc", start = 0, verify = "T"
},
profileDatFileFilters);
DataColumnCollection _dccColumnID = TempDataTable.Columns;
if (_dccColumnID.Contains("VALUE") && TempDataTable.Rows.Count > 0)
{
value = TempDataTable.Rows.Count > 0 ? TempDataTable.Rows[0].Field <byte[]>("VALUE") : new byte[0];
}
return(value);
}
else
{
return(new byte[0]);
}
}
private void grdData_GridRowDataBound(object sender, GridViewRowEventArgs e)
{
columnData.SetupGridBody(e.Row);
GridViewRow row = e.Row;
FormatRowDisplay(ref row);
if (DCC.Contains("SR_ID"))
{
row.Attributes.Add("sr_id", row.Cells[DCC.IndexOf("SR_ID")].Text);
row.Attributes.Add("sr_external", row.Cells[DCC.IndexOf("System")].Text != "WTS" ? "1" : "0");
if (searchMode)
{
var tdsr = row.Cells[DCC.IndexOf("SR #")];
tdsr.Style["text-decoration"] = "underline";
tdsr.Style["color"] = "blue";
tdsr.Style["cursor"] = "pointer";
tdsr.Attributes["tdsrnum"] = "1";
}
string[] submittedByString = row.Cells[DCC.IndexOf("Submitted By")].Text.Split('.');
if (submittedByString.Length == 2)
{
string firstName = submittedByString[0];
string lastName = submittedByString[1];
row.Cells[DCC.IndexOf("Submitted By")].Text = char.ToUpper(firstName[0]) + firstName.Substring(1).ToLower() + "." + char.ToUpper(lastName[0]) + lastName.Substring(1).ToLower();
}
else if (submittedByString.Length == 3)
{
string firstName = submittedByString[0];
string middleName = submittedByString[1];
string lastName = submittedByString[2];
row.Cells[DCC.IndexOf("Submitted By")].Text = char.ToUpper(firstName[0]) + firstName.Substring(1).ToLower() + "." + char.ToUpper(middleName[0]) + "." + char.ToUpper(lastName[0]) + lastName.Substring(1).ToLower();
}
if (this.CanEditSR)
{
if (DCC.Contains("StatusID"))
{
row.Cells[DCC.IndexOf("StatusID")].Style["text-align"] = "center";
DropDownList statusDDL = CreateDropDownList("SR", row.Cells[DCC.IndexOf("SR_ID")].Text, this.dtStatus, "Status", "Status", "StatusID", row.Cells[DCC.IndexOf("StatusID")].Text, "", null);
statusDDL.Enabled = row.Cells[DCC.IndexOf("System")].Text == "WTS" ? true : false;
row.Cells[DCC.IndexOf("StatusID")].Controls.Add(statusDDL);
}
if (DCC.Contains("Reasoning"))
{
row.Cells[DCC.IndexOf("Reasoning")].Style["text-align"] = "center";
DropDownList reasoningDDL = CreateDropDownList("SR", row.Cells[DCC.IndexOf("SR_ID")].Text, this.dtType, "Reasoning", "SRType", "SRTypeID", row.Cells[DCC.IndexOf("Type_ID")].Text, row.Cells[DCC.IndexOf("Reasoning")].Text, null);
reasoningDDL.Enabled = row.Cells[DCC.IndexOf("System")].Text == "WTS" ? true : false;
row.Cells[DCC.IndexOf("Reasoning")].Controls.Add(reasoningDDL);
}
if (DCC.Contains("User's Priority"))
{
row.Cells[DCC.IndexOf("User's Priority")].Style["text-align"] = "center";
DropDownList userPriorityDDL = CreateDropDownList("SR", row.Cells[DCC.IndexOf("SR_ID")].Text, this.dtPriority, "User's Priority", "Priority", "PriorityID", row.Cells[DCC.IndexOf("Priority_ID")].Text, row.Cells[DCC.IndexOf("User's Priority")].Text, null);
userPriorityDDL.Enabled = row.Cells[DCC.IndexOf("System")].Text == "WTS" ? true : false;
row.Cells[DCC.IndexOf("User's Priority")].Controls.Add(userPriorityDDL);
}
if (DCC.Contains("INVPriorityID"))
{
row.Cells[DCC.IndexOf("INVPriorityID")].Style["text-align"] = "center";
DropDownList INVPriorityDDL = CreateDropDownList("SR", row.Cells[DCC.IndexOf("SR_ID")].Text, this.dtPriority, "Investigation Priority", "Priority", "PriorityID", row.Cells[DCC.IndexOf("INVPriorityID")].Text, row.Cells[DCC.IndexOf("INVPriority")].Text, null);
INVPriorityDDL.Enabled = row.Cells[DCC.IndexOf("System")].Text == "WTS" ? true : false;
row.Cells[DCC.IndexOf("INVPriorityID")].Controls.Add(INVPriorityDDL);
}
if (DCC.Contains("SRRankID"))
{
row.Cells[DCC.IndexOf("SRRankID")].Style["text-align"] = "center";
DropDownList srRankDDL = CreateDropDownList("SR", row.Cells[DCC.IndexOf("SR_ID")].Text, this.dtSRRank, "SRRankID", "Priority", "PriorityID", row.Cells[DCC.IndexOf("SRRankID")].Text, "", null);
srRankDDL.Enabled = row.Cells[DCC.IndexOf("System")].Text == "WTS" ? true : false;
row.Cells[DCC.IndexOf("SRRankID")].Controls.Add(srRankDDL);
}
if (DCC.Contains("Sort"))
{
row.Cells[DCC.IndexOf("Sort")].Style["text-align"] = "center";
row.Cells[DCC.IndexOf("Sort")].Controls.Add(CreateTextBox("SR", row.Cells[DCC.IndexOf("SR_ID")].Text, "Sort", row.Cells[DCC.IndexOf("Sort")].Text, true));
}
if (DCC.Contains("TaskData"))
{
row.Cells[DCC.IndexOf("TaskData")].Style["text-align"] = "center";
string txtTask = Server.HtmlDecode(Uri.UnescapeDataString(row.Cells[DCC.IndexOf("TaskData")].Text)).Trim();
row.Cells[DCC.IndexOf("TaskData")].Controls.Add(CreateTaskLink(txtTask));
}
}
}
if (DCC.Contains("Submitted Date"))
{
DateTime nDate = new DateTime();
if (DateTime.TryParse(row.Cells[DCC.IndexOf("Submitted Date")].Text, out nDate))
{
row.Cells[DCC.IndexOf("Submitted Date")].Text = String.Format("{0:M/d/yyyy}", nDate);
}
}
if (DCC.Contains("Description"))
{
string txtDescription = Server.HtmlDecode(Uri.UnescapeDataString(row.Cells[DCC.IndexOf("Description")].Text)).Trim();
if (txtDescription.Length > 175)
{
row.Cells[DCC.IndexOf("Description")].Controls.Add(CreateTextLink(txtDescription, 175));
}
else
{
row.Cells[DCC.IndexOf("Description")].Text = txtDescription;
}
}
}
protected override bool PosvalidaCad()
{
bool bRetorno = true;
bool bUsandoWeb = (oTabelas.GetPlataformaUtilizada() ==
PlataformaUtilizada.Web);
//Verificar depois como ficará em Windows Forms como ficará a
//consistência de CNPJ / CPF, uma vez que nos sistemas Desktop
//é efetuada uma pergunta antes de checar se existe outro cliente
//com o mesmo CNPJ / CPF.
if (bUsandoWeb)
{
if (ExisteOutroClienteMesmoCNPJCPF())
{
return(false);
}
}
if (!bRetorno)
{
return(false);
}
DataColumnCollection Colunas = EstruturaDataTable.Columns;
if (RegistroAtual["ST_PESSOAJ"].ToString().Equals("N"))
{
if (Colunas.Contains("ST_SIMPLES"))
{
RegistroAtual["ST_SIMPLES"] = "N";
}
if (Colunas.Contains("ST_SIMPAUL"))
{
RegistroAtual["ST_SIMPAUL"] = "N";
}
if (Colunas.Contains("ST_ATACADISTA"))
{
RegistroAtual["ST_ATACADISTA"] = "N";
}
}
if (bUsandoWeb)
{
if (Colunas.Contains("DS_ENDCOB"))
{
RegistroAtual["DS_ENDCOB"] = RegistroAtual["DS_ENDNOR"];
}
if (Colunas.Contains("NM_BAIBROCOB"))
{
RegistroAtual["NM_BAIBROCOB"] = RegistroAtual["NM_BAIRRONOR"];
}
if (Colunas.Contains("NM_CIDCOB"))
{
RegistroAtual["NM_CIDCOB"] = RegistroAtual["NM_CIDNOR"];
}
if (Colunas.Contains("CD_UFCOB"))
{
RegistroAtual["CD_UFCOB"] = RegistroAtual["CD_UFNOR"];
}
if (Colunas.Contains("CD_CEPCOB"))
{
RegistroAtual["CD_CEPCOB"] = RegistroAtual["CD_CEPNOR"];
}
if (Colunas.Contains("CD_FONECOB"))
{
RegistroAtual["CD_FONECOB"] = RegistroAtual["CD_FONENOR"];
}
if (Colunas.Contains("CD_FAXCOB"))
{
RegistroAtual["CD_FAXCOB"] = RegistroAtual["CD_FAXNOR"];
}
if (Colunas.Contains("CD_CXPCOB"))
{
RegistroAtual["CD_CXPCOB"] = RegistroAtual["CD_CXPNOR"];
}
if (Colunas.Contains("DS_CONCOBR"))
{
RegistroAtual["DS_CONCOBR"] = RegistroAtual["DS_CONTATO"];
}
}
if (Colunas.Contains("ST_CONTRIB"))
{
bool bIsento = true;
if (RegistroAtual["CD_INSEST"] != null)
{
string sCdInsest = RegistroAtual["CD_INSEST"].ToString();
bIsento = ((sCdInsest.Equals(String.Empty)) &&
(sCdInsest.Equals("ISENTO")));
//Espeficio para Torcetex.
//for (int i = 0; i < sCdInsest.Length; i++)
//{
// if ((sCdInsest.Substring(i, 1) == "/") ||
// (sCdInsest.Substring(i, 1) == "."))
// {
// this.SetErro("Campo foi preenchido incorretamente: " +
// "campo Inscrição Estadual permite somente números");
// return false;
// }
//}
}
if (bIsento)
{
RegistroAtual["ST_CONTRIB"] = "S";
}
else
{
RegistroAtual["ST_CONTRIB"] = "N";
}
}
return(true);
}
private void grdData_GridHeaderRowDataBound(object sender, GridViewRowEventArgs e)
{
GridViewRow row = e.Row;
FormatHeaderRowDisplay(ref row);
if (this.CanEditAOR)
{
if (DCC.Contains("X"))
{
row.Cells[DCC.IndexOf("X")].Style["text-align"] = "center";
row.Cells[DCC.IndexOf("X")].Controls.Add(CreateImage());
}
}
}
protected override bool PrevalidaCad()
{
RegistroAtual["DT_CAD"] = DateTime.Now.Date;
RegistroAtual["CD_USUINC"] = oTabelas.CdUsuarioAtual;
DataColumnCollection Colunas = EstruturaDataTable.Columns;
RegistroAtual["ST_PESSOAJ"] = "S";
RegistroAtual["ST_INATIVO"] = "N";
if (Colunas.Contains("ST_ZFMALC"))
{
RegistroAtual["ST_ZFMALC"] = "N";
}
if (Colunas.Contains("CD_UFNOR"))
{
RegistroAtual["CD_UFNOR"] = "SP";
}
if (Colunas.Contains("CD_UFCOM"))
{
RegistroAtual["CD_UFCOM"] = "SP";
}
if (Colunas.Contains("CD_UFCOB"))
{
RegistroAtual["CD_UFCOB"] = "SP";
}
if (Colunas.Contains("ST_SPC"))
{
RegistroAtual["ST_SPC"] = "N";
}
if (Colunas.Contains("ST_SCI"))
{
RegistroAtual["ST_SCI"] = "S";
}
if (Colunas.Contains("ST_ACOMER"))
{
RegistroAtual["ST_ACOMER"] = "N";
}
if (Colunas.Contains("ST_CONSFINAL"))
{
RegistroAtual["ST_CONSFINAL"] = "S";
}
if (Colunas.Contains("ST_CONSUMOSUBST"))
{
RegistroAtual["ST_CONSUMOSUBST"] = "S";
}
if (Colunas.Contains("ST_CONTRIB"))
{
RegistroAtual["ST_CONTRIB"] = "S";
}
if (Colunas.Contains("ST_DESCSUFRAMA"))
{
RegistroAtual["ST_DESCSUFRAMA"] = "N";
}
if (Colunas.Contains("ST_ZERAICMS"))
{
RegistroAtual["ST_ZERAICMS"] = "N";
}
if (Colunas.Contains("ST_ZERAIPI"))
{
RegistroAtual["ST_ZERAIPI"] = "N";
}
if (Colunas.Contains("CD_PAIS"))
{
RegistroAtual["CD_PAIS"] = "1058";
}
if (Colunas.Contains("CD_CATEGO"))
{
RegistroAtual["CD_CATEGO"] = BuscaCategoria();
}
return(true);
}
public bool CheckValidExcel(DataTable table)
{
bool valid = true;
DataColumnCollection columns = table.Columns;
if (!columns.Contains("CompanyName"))
{
valid = false;
}
if (!columns.Contains("ContactName"))
{
valid = false;
}
if (!columns.Contains("ShortName"))
{
valid = false;
}
if (!columns.Contains("Email"))
{
valid = false;
}
if (!columns.Contains("TelephoneNo"))
{
valid = false;
}
if (!columns.Contains("MobileNo"))
{
valid = false;
}
if (!columns.Contains("FaxNumber"))
{
valid = false;
}
if (!columns.Contains("WorkingStartTime"))
{
valid = false;
}
if (!columns.Contains("WorkingEndTime"))
{
valid = false;
}
if (!columns.Contains("WorkingWeekDay"))
{
valid = false;
}
if (!columns.Contains("Timezone"))
{
valid = false;
}
if (!columns.Contains("Address"))
{
valid = false;
}
if (!columns.Contains("City"))
{
valid = false;
}
if (!columns.Contains("State"))
{
valid = false;
}
if (!columns.Contains("Zip"))
{
valid = false;
}
if (!columns.Contains("Country"))
{
valid = false;
}
return(valid);
}
/// <summary>
/// Данная функция является точкой входа.
/// <summary>
public void Execute(Context context)
{
Encoding.RegisterProvider(CodePagesEncodingProvider.Instance);
// Почти также как и через путь к файлу в строке (string)
using (var stream = context.ExcelFileInfo.Open(FileMode.Open, FileAccess.Read))
{
IExcelDataReader reader;
reader = ExcelReaderFactory.CreateReader(stream);
var conf = new ExcelDataSetConfiguration
{
ConfigureDataTable = _ => new ExcelDataTableConfiguration
{
// Использовать в таблицах заголовки
UseHeaderRow = true
}
};
// Получение DataSet (набор данных)
// Подробнее https://docs.microsoft.com/ru-ru/dotnet/api/system.data.dataset?view=netcore-3.1
DataSet dataSet = reader.AsDataSet(conf);
// Некоторые примеры использования:
// Получение DataTable (определенная таблица, Sheet)
// Подробнее https://docs.microsoft.com/ru-ru/dotnet/api/system.data.datatable?view=netcore-3.1
DataTable dataTable = dataSet.Tables[0];
// Получение DataRowCollection (набор строк)
DataRowCollection rows = dataTable.Rows;
// Получение DataColumnCollection (набор столбцов)
DataColumnCollection columns = dataTable.Columns;
// Проверка существует ли столбец "Итоговая стоимость"
if (columns.Contains("Итоговая стоимость"))
{
context.IsCorrectFormat = true;
// В качестве примера посчитаем сумму, но с учетом того, что последняя строка итоговая.
int rowsCount = rows.Count;
double summ = 0.0;
for (int i = 0; i < rowsCount - 1; i++)
{
summ += (double)rows[i]["Итоговая стоимость"];
}
context.Summ = summ;
// Мы можем также проверить еще что-нибудь, например, расчитанную и итоговую.
// И в случае ошибки отметить это в контексте.
if (Math.Round(summ, 2) != Math.Round((double)rows[rowsCount - 1]["Итоговая стоимость"], 2))
{
context.HasSummError = true;
}
}
else
{
// В качестве примера обозначим, что если не нашли столбец "Итоговая стоимость",
// то считаем некорректный формат. Далее это можно использовать роботом,
// например, в шлюзе и выполнить какие-то определенные действия для этого случая.
context.IsCorrectFormat = false;
}
// Подробности по объектам и по другим случаям чтения можно найти в подразделах:
// https://docs.microsoft.com/ru-ru/dotnet/api/system.data?view=netcore-3.1
}
}
void AddRepDetails()
{
if (cmbBranch.SelectedIndex > -1)
{
code = null;
if (Edition == "ePlus Professional Edition")
{
br = "HO";
Edition = "";
}
else
{
br = this.cmbBranch.GetItemText(this.cmbBranch.SelectedItem);
}
/*
* this.cmbBranch.SelectedIndexChanged -= new System.EventHandler(this.cmbBranch_SelectedIndexChanged);
* for (int i = cmbBranch.Items.Count - 1; i >= 0; i--)
* {
* if (cmbBranch.Items[i] != this.cmbBranch.SelectedItem)
* //cmbBranch.Items.RemoveAt(i);
* cmbBranch.Items[i]
* }
* this.cmbBranch.SelectedIndexChanged += new System.EventHandler(this.cmbBranch_SelectedIndexChanged);
*/
cmbBranch.Enabled = false;
//MessageBox.Show(br);
cmd.Connection = cnn;
cmd.CommandText = "SELECT CODE FROM DATABASEMASTER WHERE CODE IN (SELECT DBCODE FROM DATABASEMAPPING WHERE BRCODE = '" + br + "')";
cmd.ExecuteNonQuery();
MySqlDataReader rdr = cmd.ExecuteReader();
if (rdr.Read())
{
code = rdr.GetValue(0).ToString(); //MessageBox.Show(code);
}
rdr.Close();
if (code != null)
{
dbinfo.createLogFile(dbinfo1);
//cmd.Connection = cnn;
if (br == "HO")
{
type = "DistributedServer";
}
else
{
type = "DistributedClient";
cmd.CommandText = "delete from branchuser where usercode = 'admin' and brcode <> '" + br + "'";
cmd.ExecuteNonQuery();
}
cmd.CommandText = "UPDATE " + dbinfo1.DBName + ".setup SET `DBTYPE`='" + type + "' WHERE `CODE`='01'";
cmd.ExecuteNonQuery();
cmd.CommandText = "UPDATE SETUP SET DEFAULTBRANCH = '" + br + "' WHERE CODE = '01'";
cmd.ExecuteNonQuery();
cmd.CommandText = "update databasemaster set logdbname = '" + dbinfo1.DBName.Replace("_data", "_log") + "' , logservername = '" + dbinfo1.ServerName + "' , logport = '" + dbinfo1.DBPort + "' where code = '" + code + "'";
cmd.ExecuteNonQuery();
MessageBox.Show("Created Log Successfully");
dbinfoLog.ServerName = settings.serverName;
dbinfoLog.DBName = dbinfo1.DBName.Replace("_data", "_log");
dbinfoLog.DBPort = settings.dbPort;
dbinfoLog.DBUser = settings.userName;
dbinfoLog.DBPWD = settings.password;
connectionString = dbinfo.getConnectionString(dbinfoLog);
cnnLog = new MySqlConnection(connectionString);
openconnection(cnnLog);
cmd.CommandText = "select code from databasemaster where code not in ('" + code + "')";
cmd.ExecuteNonQuery();
rdr = cmd.ExecuteReader();
i = 0;
while (rdr.Read())
{
Repcode = rdr.GetValue(0).ToString();
//RepBrcode = rdr.GetValue(1).ToString();
// MessageBox.Show(Repcode.ToString());
//MessageBox.Show(RepBrcode.ToString());
i++;
cmd.Connection = cnnLog;
cmd.CommandText = "insert ignore into replicationinfo (code,firstid,minimumtimegap) values ('" + Repcode + "','0','60')";
try
{
cmd.ExecuteNonQuery();
}
catch (MySqlException ex)
{
MessageBox.Show("\n" + ex.ToString());
}
}
rdr.Close();
DataColumnCollection columns = dt.Columns;
if (columns.Contains("servername"))
{
dt.Columns.Remove("servername");
}
dt.Clear();
//adapterNew.Dispose();
DataColumn col = dt.Columns.Add("servername", typeof(string));
col.SetOrdinal(0);
//dt.Columns.Add(new DataColumn("ServerName", typeof(string))
adapterNew.SelectCommand = new MySqlCommand("select code,firstid,minimumtimegap from replicationinfo ", cnnLog);
try
{
adapterNew.Fill(dt);
}
catch (MySqlException ex)
{
MessageBox.Show("\n" + ex.ToString());
}
cmb = new MySqlCommandBuilder(adapterNew);
Dgv.AutoSize = true;
//Size startingSize = new Size(20, 20);
//Dgv.Size = startingSize;
Dgv.DataSource = dt;
cmd.Connection = cnn;
for (int rows = 0; rows < (Dgv.Rows.Count - 1); rows++)
{
string value = Dgv.Rows[rows].Cells["code"].Value.ToString();
cmd.CommandText = "select servername from databasemaster where code = '" + value + "'";
cmd.ExecuteNonQuery();
rdr = cmd.ExecuteReader();
if (rdr.Read())
{ //MessageBox.Show(rdr.GetValue(0).ToString());
Dgv.Rows[rows].Cells["servername"].Value = rdr.GetValue(0).ToString();
}
rdr.Close();
}
if (select == 1)
{
Dgv.ReadOnly = true;
}
Dgv.Show();
}
}
}
private T GetDbColumnValue <T>(string columnName) => _schemaColumns.Contains(columnName) && _schemaRow[columnName] is T value ? value : (default);
private void Inicializar()
{
if (Session["dsReporteTiemposClientes"] != null)
{
DataSet dsExcel = new DataSet();
dsExcel = (DataSet)Session["dsReporteTiemposClientes"];
DataGrid dg = new DataGrid();
dg.ShowFooter = false;
dg.ItemDataBound += new System.Web.UI.WebControls.DataGridItemEventHandler(dgReporteTiemposClientes_ItemDataBound);
DataColumnCollection columns = dsExcel.Tables[0].Columns;
if (!(Boolean)Session["chkDespachosRTC"])
{
if (columns.Contains("TiempoDespacho"))
{
dsExcel.Tables[0].Columns.Remove("TiempoDespacho");
}
}
if (!(Boolean)Session["chkVerTiemposTransitoRTC"])
{
if (columns.Contains("TiempoTransito"))
{
dsExcel.Tables[0].Columns.Remove("TiempoTransito");
}
}
if (!(Boolean)Session["ChkVerTiemposRecepcionRTC"])
{
if (columns.Contains("TiempoRecepcion"))
{
dsExcel.Tables[0].Columns.Remove("TiempoRecepcion");
}
}
if (!(Boolean)Session["chkEntregasRTC"])
{
if (columns.Contains("TiempoEntrega"))
{
dsExcel.Tables[0].Columns.Remove("TiempoEntrega");
}
}
if (columns.Contains("IDSesion"))
{
dsExcel.Tables[0].Columns.Remove("IDSesion");
}
if (columns.Contains("GuiaID"))
{
dsExcel.Tables[0].Columns.Remove("GuiaID");
}
if (columns.Contains("GuiaID"))
{
dsExcel.Tables[0].Columns.Remove("GuiaID");
}
if (columns.Contains("AgenciaOrigenID"))
{
dsExcel.Tables[0].Columns.Remove("AgenciaOrigenID");
}
if (columns.Contains("AgenciaDestinoID"))
{
dsExcel.Tables[0].Columns.Remove("AgenciaDestinoID");
}
if (columns.Contains("UnidadVentaId"))
{
dsExcel.Tables[0].Columns.Remove("UnidadVentaId");
}
if (columns.Contains("TipoGuia"))
{
dsExcel.Tables[0].Columns["TipoGuia"].ColumnName = "Tipo";
}
if (columns.Contains("NroSucursalGuia"))
{
dsExcel.Tables[0].Columns["NroSucursalGuia"].ColumnName = "Sucursal";
}
if (columns.Contains("NroGuia"))
{
dsExcel.Tables[0].Columns["NroGuia"].ColumnName = "Numero";
}
if (columns.Contains("RazonSocialOrigen"))
{
dsExcel.Tables[0].Columns["RazonSocialOrigen"].ColumnName = "Origen";
}
if (columns.Contains("RazonSocialDestino"))
{
dsExcel.Tables[0].Columns["RazonSocialDestino"].ColumnName = "Destino";
}
if (columns.Contains("EmpresaTercero"))
{
dsExcel.Tables[0].Columns["EmpresaTercero"].ColumnName = "Empresa Tercero";
}
if (columns.Contains("LocalidadDestino"))
{
dsExcel.Tables[0].Columns["LocalidadDestino"].ColumnName = "Localidad Destino";
}
if (columns.Contains("CP"))
{
dsExcel.Tables[0].Columns["CP"].ColumnName = "CP Destino";
}
if (columns.Contains("ClienteCodigo"))
{
dsExcel.Tables[0].Columns["ClienteCodigo"].ColumnName = "Nro. Cliente";//
}
if (columns.Contains("ClienteDescrip"))
{
dsExcel.Tables[0].Columns["ClienteDescrip"].ColumnName = "Razon Social";
}
if (columns.Contains("HorasEnContra"))
{
dsExcel.Tables[0].Columns["HorasEnContra"].ColumnName = "Horas En Contra";
}
if (columns.Contains("UnidadVentaDescrip"))
{
dsExcel.Tables[0].Columns["UnidadVentaDescrip"].ColumnName = "Unidad Venta";//
}
dsExcel.AcceptChanges();
dg.DataSource = dsExcel.Tables[0];
dg.DataBind();
this.EnableViewState = false;
Response.Clear();
Response.Buffer = true;
Response.ContentType = "application/vnd.ms-excel";
Response.Charset = "";
Response.ContentEncoding = System.Text.Encoding.Default;
Response.AddHeader("Content-Disposition", "filename=ReporteTiemposClientes.xls");
System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
dg.RenderControl(oHtmlTextWriter);
Response.Write(oStringWriter.ToString());
Response.End();
}
else
{
return;
}
}
public static T DataRowToModel <T>(DataRow dr) where T : new()
{
T t = new T();
if (dr == null)
{
return(default(T));
}
// 获得此模型的公共属性
PropertyInfo[] propertys = t.GetType().GetProperties();
DataColumnCollection Columns = dr.Table.Columns;
foreach (PropertyInfo p in propertys)
{
string columnName = p.Name;
// string columnName = p.Name;如果不用属性，数据库字段对应model属性,就用这个
if (Columns.Contains(columnName))
{
// 判断此属性是否有Setter或columnName值是否为空
object value = dr[columnName];
if (!p.CanWrite || value is DBNull || value == DBNull.Value)
{
continue;
}
switch (p.PropertyType.ToString())
{
case "System.String":
p.SetValue(t, Convert.ToString(value), null);
break;
case "System.Int32":
p.SetValue(t, Convert.ToInt32(value), null);
break;
case "System.Int64":
p.SetValue(t, Convert.ToInt64(value), null);
break;
case "System.DateTime":
p.SetValue(t, Convert.ToDateTime(value), null);
break;
case "System.Boolean":
p.SetValue(t, Convert.ToBoolean(value), null);
break;
case "System.Double":
p.SetValue(t, Convert.ToDouble(value), null);
break;
case "System.Decimal":
p.SetValue(t, Convert.ToDecimal(value), null);
break;
default:
p.SetValue(t, value, null);
break;
}
}
}
return(t);
}
public MapaColunaPortador CriaMapaColuna(DataRow dr)
{
DataColumnCollection dcc = dr.Table.Columns;
MapaColunaPortador mcp = new MapaColunaPortador()
{
TpIdentif = -1,
Identificacao = -1,
CPF = -1,
Nome = -1,
NomeFacial = -1,
DtNascimento = -1,
Sexo = -1,
CnpjFilial = -1,
Grupo = -1,
Email = -1,
DDDCel = -1,
Celular = -1,
NomeMae = -1,
IdRegistro = -1
};
if (dcc.Contains("TpIdentif"))
{
mcp.TpIdentif = dcc["TpIdentif"].Ordinal;
}
if (dcc.Contains("Identificacao"))
{
mcp.Identificacao = dcc["Identificacao"].Ordinal;
}
if (dcc.Contains("CPF"))
{
mcp.CPF = dcc["CPF"].Ordinal;
}
if (dcc.Contains("Nome"))
{
mcp.Nome = dcc["Nome"].Ordinal;
}
if (dcc.Contains("NomeFacial"))
{
mcp.NomeFacial = dcc["NomeFacial"].Ordinal;
}
if (dcc.Contains("DtNascimento"))
{
mcp.DtNascimento = dcc["DtNascimento"].Ordinal;
}
if (dcc.Contains("Sexo"))
{
mcp.Sexo = dcc["Sexo"].Ordinal;
}
if (dcc.Contains("CnpjFilial"))
{
mcp.CnpjFilial = dcc["CnpjFilial"].Ordinal;
}
if (dcc.Contains("Grupo"))
{
mcp.Grupo = dcc["Grupo"].Ordinal;
}
if (dcc.Contains("Email"))
{
mcp.Email = dcc["Email"].Ordinal;
}
if (dcc.Contains("DDDCel"))
{
mcp.DDDCel = dcc["DDDCel"].Ordinal;
}
if (dcc.Contains("Celular"))
{
mcp.Celular = dcc["Celular"].Ordinal;
}
if (dcc.Contains("NomeMae"))
{
mcp.NomeMae = dcc["NomeMae"].Ordinal;
}
if (dcc.Contains("IdRegistro"))
{
mcp.IdRegistro = dcc["IdRegistro"].Ordinal;
}
return(mcp);
}
private void getFromISBN(Books bookD, string isbn)
{
String ISBN = isbn;
try
{
System.Net.HttpWebRequest oHTTPRequest = System.Net.HttpWebRequest.Create("https://www.googleapis.com/books/v1/volumes?q=isbn:" + ISBN) as System.Net.HttpWebRequest;
oHTTPRequest.ContentType = "Json";
string result = "";
try
{
Stream response = oHTTPRequest.GetResponse().GetResponseStream();
StreamReader reader = new StreamReader(response);
result = reader.ReadToEnd();
}
finally
{
}
System.Xml.XmlDocument doc = JsonConvert.DeserializeXmlNode(result, "root");
XmlReader xmlReader = new XmlNodeReader(doc);
DataSet ds = new DataSet();
ds.ReadXml(xmlReader);
if (ds.Tables.Contains("industryIdentifiers"))
{
DataColumnCollection columns = ds.Tables["industryIdentifiers"].Columns;
if (columns.Contains("identifier"))
{
if (ds.Tables["IndustryIdentifiers"].Rows[0]["type"].ToString() == "ISBN_13" || ds.Tables["IndustryIdentifiers"].Rows[0]["type"].ToString() == "OTHER")
{
bookD.ISBN = ds.Tables["industryIdentifiers"].Rows[0]["identifier"].ToString();
}
else
{
bookD.ISBN = ds.Tables["industryIdentifiers"].Rows[1]["identifier"].ToString();
}
}
else
{
bookD.ISBN = "";
}
}
if (ds.Tables.Contains("imageLinks"))
{
DataColumnCollection columns = ds.Tables["imageLinks"].Columns;
if (columns.Contains("thumbnail"))
{
string sURL = ds.Tables["imageLinks"].Rows[0]["thumbnail"].ToString().Trim();
System.Net.WebClient webClient = new System.Net.WebClient();
//bookD.img = webClient.DownloadData(sURL);
//img = webClient.DownloadData(sURL);
//imgBook.Value = sURL;
}
else
{
}
}
if (ds.Tables.Contains("volumeInfo"))
{
DataColumnCollection columns = ds.Tables["volumeInfo"].Columns;
string title;
if (columns.Contains("Title"))
{
title = ds.Tables["volumeInfo"].Rows[0]["title"].ToString();
bookD.Title = title.Replace("'", "");
}
else
{
bookD.Title = "";
}
string subtitle;
if (columns.Contains("Subtitle"))
{
subtitle = ds.Tables["volumeInfo"].Rows[0]["Subtitle"].ToString();
bookD.SubTitle = subtitle.Replace("'", "");
}
else
{
bookD.SubTitle = "";
}
string catgory;
if (columns.Contains("categories"))
{
catgory = ds.Tables["volumeInfo"].Rows[0]["categories"].ToString();
bookD.Category = catgory.Replace("'", "");
}
else
{
bookD.Category = "";
}
string lang;
if (columns.Contains("Language"))
{
lang = ds.Tables["volumeInfo"].Rows[0]["Language"].ToString();
bookD.Language = lang.Replace("'", "");
}
else
{
bookD.Language = "English";
}
string desc;
if (columns.Contains("Description"))
{
desc = ds.Tables["volumeInfo"].Rows[0]["Description"].ToString();
bookD.Description = desc.Replace("'", "");
}
else
{
bookD.Description = "";
}
string pub;
if (columns.Contains("publisher"))
{
pub = ds.Tables["volumeInfo"].Rows[0]["publisher"].ToString();
bookD.Publisher = pub.Replace("'", "");
}
else
{
bookD.Publisher = "";
}
string aut1;
string aut2;
if (ds.Tables.Contains("authors"))
{
if (ds.Tables["authors"].Rows.Count > 0)
{
if (ds.Tables["authors"].Rows.Count <= 2)
{
aut1 = ds.Tables["authors"].Rows[0]["authors_Text"].ToString();
aut2 = ds.Tables["authors"].Rows[1]["authors_Text"].ToString();
bookD.Author1 = aut1.Replace("'", "");
bookD.Author2 = aut2.Replace("'", "");
}
}
}
else if (columns.Contains("authors"))
{
aut1 = ds.Tables["volumeInfo"].Rows[0]["authors"].ToString();
bookD.Author1 = aut1.Replace("'", "");
}
else
{
bookD.Author1 = "";
}
}
else
{
System.Net.HttpWebRequest oHTTPRequest1 = System.Net.HttpWebRequest.Create("http://xisbn.worldcat.org/webservices/xid/isbn/ " + isbn + "?method=getMetadata&format=xml&fl=*&callback=mymethod") as System.Net.HttpWebRequest;
oHTTPRequest1.ContentType = "xml";
string result1 = "";
try
{
Stream response1 = oHTTPRequest1.GetResponse().GetResponseStream();
StreamReader reader1 = new StreamReader(response1);
result1 = reader1.ReadToEnd();
}
finally
{
}
System.Xml.XmlDocument doc1 = new System.Xml.XmlDocument();
doc1.LoadXml(result1);
XmlReader xmlReaderX = new XmlNodeReader(doc1);
DataSet ds1 = new DataSet();
ds.ReadXml(xmlReader);
if (ds1.Tables.Contains("isbn"))
{
DataColumnCollection columns = ds1.Tables["isbn"].Columns;
if (columns.Contains("isbn_Text"))
{
bookD.ISBN = ds1.Tables["isbn"].Rows[0]["isbn_Text"].ToString();
string pub2;
if (columns.Contains("publisher"))
{
pub2 = ds1.Tables["isbn"].Rows[0]["publisher"].ToString();
bookD.Publisher = pub2.Replace("'", "");
}
else
{
bookD.Publisher = "";
}
string auth1;
if (columns.Contains("author"))
{
auth1 = ds1.Tables["isbn"].Rows[0]["author"].ToString();
bookD.Author1 = auth1.Replace("'", "");
}
else
{
bookD.Author1 = "";
}
string title1;
if (columns.Contains("title"))
{
title1 = ds1.Tables["isbn"].Rows[0]["title"].ToString();
bookD.Title = title1.Replace("'", "");
}
else
{
bookD.Title = "";
}
if (columns.Contains("lang"))
{
bookD.Language = ds1.Tables["isbn"].Rows[0]["lang"].ToString();
}
else
{
bookD.Language = "";
}
}
else
{
bookD.ISBN = "";
}
}
else
{
//useISBNSearchAPI(isbn);
}
}
bookD.ISBN = ISBN;
}
catch (System.Exception)
{
}
}
